Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vba code to delete null rows after criteria found | Excel Programming | |||
Code help, delete rows based on column criteria | Excel Discussion (Misc queries) | |||
Further help on delete criteria found | Excel Programming | |||
Further help on delete criteria found | Excel Programming | |||
Further help on delete criteria found | Excel Programming |