Remove all rows that Subtotal Zero

[LoraKnight]

I need a code to run to remove all rows that Subtotal 0 in the "Other Dr" column
Here is an example of the sheet.

Practice Code Date Svc From Pat Person Nbr Pat First Name Pat Last Name Primary Doctor Other Dr
GREE 2/2/2015 55 Nick Smith 99233
2/2/2015 Count 1 0
GREE 2/3/2015 55 Nick Smith 99233
GREE 2/3/2015 55 Nick Smith 99212
2/3/2015 Count 1 1
GREE 2/4/2015 55 Nick Smith 99233
GREE 2/4/2015 55 Nick Smith 99212
2/4/2015 Count 1 1


In this example I would like to remove the 1st two rows of data because the "Other Dr" subtotal = 0
I need to leave all other data because the "Other Dr" subtotal is Greater than 0

I will be putting this formula in "Developer" Insert Module and Running it on an Excel 7 file

Thank you for your help

 

[Rick Rothstein]

I need a code to run to remove all rows that Subtotal 0 in the "Other Dr" column
Here is an example of the sheet.

Practice Code Date Svc From Pat Person Nbr Pat First Name Pat Last Name Primary Doctor Other Dr
GREE 2/2/2015 55 Nick Smith 99233
2/2/2015 Count 1 0
GREE 2/3/2015 55 Nick Smith 99233
GREE 2/3/2015 55 Nick Smith 99212
2/3/2015 Count 1 1
GREE 2/4/2015 55 Nick Smith 99233
GREE 2/4/2015 55 Nick Smith 99212
2/4/2015 Count 1 1

In this example I would like to remove the 1st two rows of data because the "Other Dr" subtotal = 0
I need to leave all other data because the "Other Dr" subtotal is Greater than 0

It is hard to tell from your posted table... is the word "Count" in a column by itself or is it in the same cell as the date?

Can we assume that the 0 in the "Other Dr" column that you want to check for being 0 or not is always on row where Count is shown (which column depending on your answer to my first question)?

 

[LoraKnight]

Sorry for the table. I couldn't get it to keep the formatting
The row that has the Date and Count and two numbers is the subtotals

The row headers look like this
Facility Date Acct First Last PrimaryDr OtherDr

subtotals will have the date Count and a number under PrimaryDr and a number under Other Dr
If the last number in the subtotal row with the word Count is a Zero, I want to delete all associated rows

 

[Rick Rothstein]

Sorry for the table. I couldn't get it to keep the formatting

Click the "Go Advanced" button at the bottom of the Reply window, click the first icon on the last row of icons... it will ask you for a grid size... input the number of rows and columns, then fill in the grid it produces... that will hold the layout. Alternately, you can use the add-in shown in my signature line... it will allow you to select an area of a worksheet, copy it, then paste it into a Reply window directly.

 

[LoraKnight]

Still having trouble formatting table, may I email you the table?

 

[LoraKnight]

The chart is the same in the previous post I posted at this link http://www.mrexcel.com/forum/excel-questions/709161-remove-all-rows-dont-subtotal-zero.html
The only difference is (Appt Status is now Primary Dr) and (CPT is now Other Dr)
In the previous post I wanted to remove all rows that were associated with a subtotal greater than 0 in the CPT column
Now I want to remove all rows that are associated with a subtotal = 0 in the (CPT / Other Dr) Column

 

[LoraKnight]

The code I have removes all rows that subtotal "CPT" column is greater than zero.
I want to change to code to delete all rows where the subtotal of that column equals zero.
My "CPT" column is now labeled "Other Dr"


Sub RowsRemoval()

Dim i, j, thelastrow As Long

thelastrow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row

For i = thelastrow To 1 Step -1
If InStr(1, Cells(i, 3), "Count") <> 0 Then
If Cells(i, 7).Value <> "0" Then
For j = i To 1 Step -1
If Cells(j, 7).Value = "CPT" Then Exit Sub
If InStr(1, Cells(j, 3), "Count") <> 0 And i <> j Then
Exit For
Else
Rows(j).EntireRow.Delete
End If
Next j
End If
End If
Next i

End Sub

 

[LoraKnight]

please help change the code to remove rows that subtotal zero

 

[LoraKnight]

Re: Remove all rows that Subtotal Zero



The code I have removes all rows that subtotal "CPT" column is greater than zero.
I want to change to code to delete all rows where the subtotal of that column equals zero.
My "CPT" column is now labeled "Other Dr"


Sub RowsRemoval()

Dim i, j, thelastrow As Long

thelastrow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row

For i = thelastrow To 1 Step -1
If InStr(1, Cells(i, 3), "Count") <> 0 Then
If Cells(i, 7).Value <> "0" Then
For j = i To 1 Step -1
If Cells(j, 7).Value = "CPT" Then Exit Sub
If InStr(1, Cells(j, 3), "Count") <> 0 And i <> j Then
Exit For
Else
Rows(j).EntireRow.Delete
End If
Next j
End If
End If
Next i

End Sub