I want a macro to look at col A row 1 and go through each cell in col A starting from the top until data is found in a cell. When this data is found, delete all rows above it.
I want a macro to look at col A row 1 and go through each cell in col A starting from the top until data is found in a cell. When this data is found, delete all rows above it.
How about:
Sub RowKiller() Dim rKill As Range Set rKill = Nothing Dim L As Long For L = 1 To Rows.Count If Cells(L, 1) = "" Then If rKill Is Nothing Then Set rKill = Cells(L, 1) Else Set rKill = Union(rKill, Cells(L, 1)) End If Else Exit For End If Next If rKill Is Nothing Then Else rKill.EntireRow.Delete End If End Sub
Gary's Student
Thanks!!! Perfect.
Thank you for the feedback.
Also Jakob, one more thing. How would I alter the code so that when it finds the 3rd used cell that has data in it, it deletes all above it?
I would use a slightly different approach:
Sub RowKiller() Dim rKill As Range Set rKill = Nothing k = 0 Dim L As Long For L = 1 To Rows.Count If Cells(L, 1) = "" Then Else k = k + 1 If k = 3 Then Set rKill = Range("A1:A" & L - 1) Exit For End If End If Next If rKill Is Nothing Then Else rKill.EntireRow.Delete End If End Sub
Thanks Jakob. One last issue now and I am finished!.....How can I add an error handler so that if it does not find the 3rd used cell with data in it, that it exits the sub?
That should already happen??
I should re-word it. I want it so that if it doesnt find the 3rd used cell with data, then to delete ALL the rows on the sheet
Sub RowKiller() Dim rKill As Range Set rKill = Nothing k = 0 Dim L As Long For L = 1 To Rows.Count If Cells(L, 1) = "" Then Else k = k + 1 If k = 3 Then Set rKill = Range("A1:A" & L - 1) Exit For End If End If Next If rKill Is Nothing Then Cells.EntireRow.Delete Else rKill.EntireRow.Delete End If End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks