Deleting series of repeating rows

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I will be as brief as possible.

The worksheet was created using the text to columns
wizard. The data was originally in Word format. I
successfully placed all the data in applicable columns and
ran a macro to delete all empty rows remaining from the
conversion from Word to Excel.

What remains in the worksheet is the page headers from
it's Word format. The headers appear at every 21st row,
consuming 7 rows.

To be completely sure that all of the unwanted text is
deleted, as the worksheet will be hundreds of pages, can a
macro be created that will delete exactly what text I
specify. I would ask this assuming that the row sequence
is broken futher down in the document (but I don't think
that is the case). Either way, I am looking for the macro
that will delete specific rows in sequence or specific
text.

Any macro will do. I just need them to disappear!

Thanks,

Bob
 
This is not a macro, but can you simply sort the data and
find the "cuplit" row and delete them? If you need them
in the same order, you might insert a column and number
it down, which would allow you to resort and put back in
the original order.
If there is enough consistancy ie it is in fact every
21rst row and is 7 row down, a macro can be written to do
it.
 
Let's assume the original text in on Sheet1, in column A. On Sheet2, in
column A, copy the text of the rows you want to delete. Then, the following
macro should delete the rows:

Public Sub DeleteRows()
Dim nRow As Long

nRow = 1
While nRow <= Worksheets("Sheet1").UsedRange.Rows.Count
If fDeleteRow(Worksheets("Sheet1").Cells(nRow, 1).Value) Then
Worksheets("Sheet1").Cells(nRow, 1).Delete xlShiftUp
Else
nRow = nRow + 1
End If
Wend
End Sub

Private Function fDeleteRow(sInString As String) As Boolean
Dim nRow As Long

For nRow = 1 To Worksheets("Sheet2").UsedRange.Rows.Count
If Worksheets("Sheet2").Cells(nRow, 1).Value = sInString Then
fDeleteRow = True
Exit Function
End If
Next nRow
fDeleteRow = False
End Function

Let me know how you get on,
Paul
 
Sorry for the delay. I've been away. I am going to play
with this today. I will let you know how it goes.

Thanks Again and Happy Holidays.

Bob
 
Back
Top