Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba 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 019-70-6226 SOLTAX EARNINGS NO 1 SOLTAX EARNINGS NO 2 GROUP TOTALS SOLTAX EARNINGS NO 1 SOLTAX EARNINGS NO 2 578-94-0005 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
|
|||
|
|||
vba code to delete null rows after criteria found
This Should do it.
This will go down the sheet and find the first occurrnce of Group Totals in Colum F Once Found it will delete this row and then continue down the list and delete any row with data missing from Column F or any occurence of "Group Total" in this same column. Cheers, Clint It presumes there is Data always in Row 7 Sub CleanUp() Dim Count as Integer, Rowz as integer Rowz = Activesheet.Cells(Rows.Count, "G").End(xlUp).Row Count =1 DO Count = Count +1 Loop Until instr(Ucase(Activesheet.cells(Count,6)), "GROUP TOTALS") 0 or Count =Rowz DO IF instr(Ucase(Activesheet.cells(Count,6)), "GROUP TOTALS") 0 THEN Activesheet.rows(Trim(Str(Count)) & ":" & trim(Str(Count))).delete IF Trim(Activesheet.cells(Count,6)) = "" THEN Activesheet.rows(Trim(Str(Count)) & ":" & trim(Str(Count))).delete Count = Count + 1 Loop until Count = Rowz End Sub "tbmarlie" wrote in message ups.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 019-70-6226 SOLTAX EARNINGS NO 1 SOLTAX EARNINGS NO 2 GROUP TOTALS SOLTAX EARNINGS NO 1 SOLTAX EARNINGS NO 2 578-94-0005 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Null/Blank Rows | Excel Discussion (Misc queries) | |||
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 |