Conditional Row Selection
Posted by Cory on June 25, 2001 8:42 AM
This one should be easy, but I still need some help: I have a sheet that was FTP'd in and it contains the headers that would've shown up if the document was printed in its original program. I'm trying to automate by use of a command button the deletion of these headers. They don't occur a set distance from one another, but they do contain the same text. Each header is five rows deep.
Example:
Company Name
Company Address
Wharehouse Number
Report Dates
Report Times
[Big block of data that needs to be kept]
Company Name
Company Address
etc., etc...
I tried using a macro with the find control (Cont + F) to search for the data, activate the cell where the data occurs, use the offset property to go to the beginning of the header, select that row and the five underneath, delete those rows, them start again looking for the next occurence of the header.
I've gotten it to find the first header, but I can't get it to do the rest without excel selecting a specific range of cells (the headers aren't equidistant from one another).
HELP!!!!!!
Posted by Ben O. on June 25, 2001 9:30 AM
Try my conditional row delete macro. Simply select a cell in row that you want deleted. The macro will search that cell in every row and delete the row when the cell has the same value. You should be able to clean up your spreadsheet with a few runs of the macro. Just replace the 6000 in rEnd = 6000 with the last row of your data. You can also add Application.ScreenUpdating = False to the beginning of the macro to speed it up. I hope this helps.
-Ben
Sub ConditionalRowDelete()
Dim myRow As Integer
Dim myCol As Integer
Dim Counter As Integer
Counter = 0
myCol = ActiveCell.Column
cValue = ActiveCell.Value
rBegin = 2
' Replace 6000 with the last row of your data
rEnd = 6000
For myRow = rEnd To rBegin Step -1
Application.StatusBar = Counter & " rows deleted."
If Cells(myRow, myCol).Value = cValue Then
Cells(myRow, myCol).EntireRow.Delete
Counter = Counter + 1
End If
Next myRow
Application.StatusBar = False
x = MsgBox(Counter & " rows deleted.", vbOKOnly, "Rows Deleted")
End Sub
Posted by Cory on June 25, 2001 11:13 AM
That worked great for the row specified by your code, but is there any way to make it delete that row and the four rows below it?
