code to delete null rows after criteria found

[tbmarlie]

I'm trying to create some vba code to delete any rows that match a
specific criteria plus any null rows below that row. See below for
some sample data:

column f column g
xxx-xx-xxx SOLTAX EARNINGS NO 1
SOLTAX EARNINGS NO 2
GROUP TOTALS SOLTAX EARNINGS NO 1
SOLTAX EARNINGS NO 2
xxx-xx-xxx INCENTIVE PAYMENTS YTD
EXCLUDED FROM FICA-HI YTD


So, I would want to delete any row with the criteria, "GROUP TOTALS"
in column f plus any row(s) below the GROUP TOTALS row that has a
null
in column f. It would do this until it reached the bottom of the
overall data. In this example, there would be 2 rows that need to be
deleted - The GROUP TOTALS row and the row immediately below it, but
there could be a variable number of blank rows below the GROUP TOTALS
row. Thanks

 

[Zone]

tbmarlie,
This is a fairly simple operation except what to do at the bottom of the
data. If care isn't taken, the code could easily just go into an endless
loop deleting blank rows after the bottom of the data. If row 1 is a
heading row and the data starts at row 2, and IF there are no gaps (empty
cells) in column G, and IF there is at least one empty row after the data,
this should work for you. James

Sub Out()
Dim k As Long
For k = Cells(2, "g").End(xlDown).Row To 2 Step -1
If Cells(k, "f") = "GROUP TOTALS" Then
While Cells(k + 1, "f") = "" And Cells(k + 1, "g") <> ""
Rows(k + 1).EntireRow.Delete
Wend
Rows(k).EntireRow.Delete
End If
Next k
End Sub

 

[tbmarlie]

tbmarlie,
This is a fairly simple operation except what to do at the bottom of the
data. If care isn't taken, the code could easily just go into an endless
loop deleting blank rows after the bottom of the data. If row 1 is a
heading row and the data starts at row 2, and IF there are no gaps (empty
cells) in column G, and IF there is at least one empty row after the data,
this should work for you. James

Sub Out()
Dim k As Long
For k = Cells(2, "g").End(xlDown).Row To 2 Step -1
If Cells(k, "f") = "GROUP TOTALS" Then
While Cells(k + 1, "f") = "" And Cells(k + 1, "g") <> ""
Rows(k + 1).EntireRow.Delete
Wend
Rows(k).EntireRow.Delete
End If
Next k
End Sub








- Show quoted text -

Worked great except that it also deleted the header row.

 

[Zone]

I don't see how that could happen, since it only goes up to row 2. You mean
it deleted row 1?

 

[tbmarlie]

I don't see how that could happen, since it only goes up to row 2. You mean
it deleted row 1?







- Show quoted text -

My mistake - it worked perfect. There was some other code that I had
in my macro that was deleting the first row. Thanks again.

 

[Zone]

You're welcome. Glad you got it straightened out. James