Remove all Rows that don't subtotal Zero

[LoraKnight]

How can I remove rows from my spreadsheet that don't subtotal Zero in the Subtotaled "CPT" Column. I only want to leave the group of data that has a subtotal of Zero in the "CPT" Column.

This is the complete Sheet

Practice Code	Appt Date	Pat Person Nbr	Pat First Name	Pat Last Name	Appt Status	CPT	Appt Status Desc	Reason Desc	Appt Provider Name
LKMN	1/2/2013	3241.1	A	Smith	T		Occurred	Ob Visit	Smith, Doctor
LKMN	1/2/2013	3241.1	A	Smith		99213,TH	TH	Office Visit-Est Patient; Intermed	Smith, Doctor
		3241.1 Count			1	1
LKMN	1/2/2013	31064.3	B	Jones	T		Occurred	Post Op	Smith, Doctor
LKMN	1/2/2013	31064.3	B	Jones				Office Visit-Est Patient; Intermed	Smith, Doctor
		31064.3 Count			1	0
LKMN	1/2/2013	40300	C	Johnson	T		Occurred	Ob Visit	Smith, Doctor
LKMN	1/2/2013	40300	C	Johnson		99213,TH	TH	Office Visit-Est Patient; Intermed	Smith, Doctor
		40300 Count			1	1
LKMN	1/2/2013	49746.1	D	Williams	T		Occurred	Problem Check	Smith, Doctor
LKMN	1/2/2013	49746.1	D	Williams		99213		Office Visit-Est Patient; Intermed	Smith, Doctor
		49746.1 Count			1	1
LKMN	1/2/2013	52680.3	E	Roberts	T		Occurred	Ob Visit	Smith, Doctor
LKMN	1/2/2013	52680.3	E	Roberts			TH	Office Visit-Est Patient; Intermed	Smith, Doctor
		52680.3 Count			1	0
LKMN	1/2/2013	75595.1	F	Lard	T		Occurred	Ob Visit	Smith, Doctor
LKMN	1/2/2013	75595.1	F	Lard		99213,TH	TH	Office Visit-Est Patient; Intermed	Smith, Doctor
		75595.1 Count			1	1
LKMN	1/2/2013	104405	G	Wilks	T		Occurred	Ob Visit	Smith, Doctor
LKMN	1/2/2013	104405	G	Wilks		99213,TH	TH	Office Visit-Est Patient; Intermed	Smith, Doctor
		104405 Count			1	1
LKMN	1/2/2013	119088	J	Smith	T		Occurred	Ob Visit	Smith, Doctor
LKMN	1/2/2013	119088	J	Smith			TH	Office Visit-Est Patient; Intermed	Smith, Doctor
		119088 Count			1	0
LKMN	1/2/2013	123931	K	Nolan	T		Occurred	Ob Visit	Smith, Doctor
LKMN	1/2/2013	123931	K	Nolan		99213,TH	TH	Office Visit-Est Patient; Intermed	Smith, Doctor
		123931 Count			1	1
LKMN	1/2/2013	164819	L	Morris	T		Occurred	Ob Visit	Smith, Doctor
LKMN	1/2/2013	164819	L	Morris		59400,X		Routine Obstetric Care Includi Ng a	Smith, Doctor
		164819 Count			1	1

<TBODY>
</TBODY>

This is what I would like to be left with after the removal of the rows that don't subtotal Zero in the CPT Column Practice Code Appt Date Pat Person Nbr Pat First Name Pat Last Name Appt Status CPT Appt Status Desc Reason Desc Appt Provider Name LKMN 1/2/2013 31064.3 B Jones T Occurred Post Op Smith, Doctor LKMN 1/2/2013 31064.3 B Jones Office Visit-Est Patient; Intermed Smith, Doctor 31064.3 Count 1 0 LKMN 1/2/2013 52680.3 E Roberts T Occurred Ob Visit Smith, Doctor LKMN 1/2/2013 52680.3 E Roberts TH Office Visit-Est Patient; Intermed Smith, Doctor 52680.3 Count 1 0 LKMN 1/2/2013 119088 J Smith T Occurred Ob Visit Smith, Doctor LKMN 1/2/2013 119088 J Smith TH Office Visit-Est Patient; Intermed Smith, Doctor 119088 Count 1 0 <TBODY> </TBODY>

<TBODY>
</TBODY>

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG18Jun39
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
    [COLOR="Navy"]If[/COLOR] Dn(Dn.Count + 1, 7) <> 0 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Dn.Resize(Dn.Count + 1)
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn.Resize(Dn.Count + 1))
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Taisho]

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]

I apologize for the simplicity of this follow up question, but where do I go on my worksheet to insert this code?

 

[LoraKnight]

.

 

[Taisho]

There are many options, I suggest right click on sheet name, display source code and then right click in VBA Project window (left side of the screen by default), insert module, then copy paste.

 

[MickG]

Click "Alt +F11" in Data sheet, Vb window appears.
From the VB window Menu Bar select "Insert" , "Module", New vb Window appears.
Paste code into this window.
Close VB window

To run code:-
Clik "Alt + F8", Macro Dialog Box Appears, Select the name of the of the Macro from the List, From the right hand side of Macro window select "Run"
Code should now be Run and sheet updated.

 

[LoraKnight]

click "alt +f11" in data sheet, vb window appears.
From the vb window menu bar select "insert" , "module", new vb window appears.
Paste code into this window.
Close vb window

to run code:-
clik "alt + f8", macro dialog box appears, select the name of the of the macro from the list, from the right hand side of macro window select "run"
code should now be run and sheet updated.

thank you - this all worked!!!!

 

[MickG]

You're welcome
Regrds Mick

 

[LoraKnight]

In this same example could you help me with the code to remove all of the rows that subtotaled 0 in the CPT column?