code to delete null rows after criteria found
 

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

code to delete null rows after criteria found
 

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" wrote in message
oups.com...
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

code to delete null rows after criteria found
 

On Oct 11, 3:51 am, "Zone" wrote:
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" wrote in message

oups.com...



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- Hide quoted text -

- Show quoted text -

Worked great except that it also deleted the header row.

code to delete null rows after criteria found
 

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

"tbmarlie" wrote in message
ups.com...
On Oct 11, 3:51 am, "Zone" wrote:
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" wrote in message

oups.com...



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- Hide quoted text -

- Show quoted text -

Worked great except that it also deleted the header row.

code to delete null rows after criteria found
 

On Oct 11, 10:37 am, "Zone" wrote:
I don't see how that could happen, since it only goes up to row 2. You mean
it deleted row 1?

"tbmarlie" wrote in message

ups.com...



On Oct 11, 3:51 am, "Zone" wrote:
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 blankrowsafterthe 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 rowafterthe
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" wrote in message

groups.com...

I'm trying to create some vba code todeleteanyrowsthat match a
specificcriteriaplus anynullrowsbelow 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 todeleteany row with thecriteria, "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 2rowsthat need to be
deleted - The GROUP TOTALS row and the row immediately below it, but
there could be a variable number of blankrowsbelow the GROUP TOTALS
row. Thanks- Hide quoted text -

- Show quoted text -

Worked great except that it also deleted the header row.- Hide quoted text -

- 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.

code to delete null rows after criteria found
 

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

"tbmarlie" wrote in message
ups.com...
On Oct 11, 10:37 am, "Zone" wrote:
I don't see how that could happen, since it only goes up to row 2. You
mean
it deleted row 1?

"tbmarlie" wrote in message

ups.com...



On Oct 11, 3:51 am, "Zone" wrote:
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 blankrowsafterthe 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 rowafterthe
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" wrote in message

groups.com...

I'm trying to create some vba code todeleteanyrowsthat match a
specificcriteriaplus anynullrowsbelow 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 todeleteany row with thecriteria, "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 2rowsthat need to be
deleted - The GROUP TOTALS row and the row immediately below it, but
there could be a variable number of blankrowsbelow the GROUP TOTALS
row. Thanks- Hide quoted text -

- Show quoted text -

Worked great except that it also deleted the header row.- Hide quoted
text -

- 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.