Remove all Rows that don't subtotal Zero

L

LoraKnight

New Member
Joined
Jun 18, 2013
Messages
14
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
[TABLE="width: 818"]
<TBODY>[TR]
[TD="class: xl65, width: 92"]Practice Code
[/TD]
[TD="class: xl65, width: 77"]Appt Date
[/TD]
[TD="class: xl65, width: 100"]Pat Person Nbr
[/TD]
[TD="class: xl65, width: 98"]Pat First Name
[/TD]
[TD="class: xl65, width: 95"]Pat Last Name
[/TD]
[TD="class: xl65, width: 78"]Appt Status
[/TD]
[TD="class: xl65, width: 77"]CPT
[/TD]
[TD="class: xl65, width: 111"]Appt Status Desc
[/TD]
[TD="class: xl65, width: 227"]Reason Desc
[/TD]
[TD="class: xl65, width: 135"]Appt Provider Name
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]LKMN
[/TD]
[TD="class: xl66, bgcolor: transparent"]1/2/2013
[/TD]
[TD="class: xl65, bgcolor: transparent"]3241.1
[/TD]
[TD="class: xl65, bgcolor: transparent"]A
[/TD]
[TD="class: xl65, bgcolor: transparent"]Smith
[/TD]
[TD="class: xl65, bgcolor: transparent"]T
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Occurred
[/TD]
[TD="class: xl65, bgcolor: transparent"]Ob Visit
[/TD]
[TD="class: xl65, bgcolor: transparent"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65"]LKMN
[/TD]
[TD="class: xl66"]1/2/2013
[/TD]
[TD="class: xl65"]3241.1
[/TD]
[TD="class: xl65"]A
[/TD]
[TD="class: xl65"]Smith
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]99213,TH
[/TD]
[TD="class: xl65"]TH
[/TD]
[TD="class: xl65"]Office Visit-Est Patient; Intermed
[/TD]
[TD="class: xl65"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]3241.1 Count
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65"]LKMN
[/TD]
[TD="class: xl66"]1/2/2013
[/TD]
[TD="class: xl65"]31064.3
[/TD]
[TD="class: xl65"]B
[/TD]
[TD="class: xl65"]Jones
[/TD]
[TD="class: xl65"]T
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Occurred
[/TD]
[TD="class: xl65"]Post Op
[/TD]
[TD="class: xl65"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]LKMN
[/TD]
[TD="class: xl66, bgcolor: transparent"]1/2/2013
[/TD]
[TD="class: xl65, bgcolor: transparent"]31064.3
[/TD]
[TD="class: xl65, bgcolor: transparent"]B
[/TD]
[TD="class: xl65, bgcolor: transparent"]Jones
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Office Visit-Est Patient; Intermed
[/TD]
[TD="class: xl65, bgcolor: transparent"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"]31064.3 Count
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]LKMN
[/TD]
[TD="class: xl66, bgcolor: transparent"]1/2/2013
[/TD]
[TD="class: xl65, bgcolor: transparent"]40300
[/TD]
[TD="class: xl65, bgcolor: transparent"]C
[/TD]
[TD="class: xl65, bgcolor: transparent"]Johnson
[/TD]
[TD="class: xl65, bgcolor: transparent"]T
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Occurred
[/TD]
[TD="class: xl65, bgcolor: transparent"]Ob Visit
[/TD]
[TD="class: xl65, bgcolor: transparent"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65"]LKMN
[/TD]
[TD="class: xl66"]1/2/2013
[/TD]
[TD="class: xl65"]40300
[/TD]
[TD="class: xl65"]C
[/TD]
[TD="class: xl65"]Johnson
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]99213,TH
[/TD]
[TD="class: xl65"]TH
[/TD]
[TD="class: xl65"]Office Visit-Est Patient; Intermed
[/TD]
[TD="class: xl65"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]40300 Count
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65"]LKMN
[/TD]
[TD="class: xl66"]1/2/2013
[/TD]
[TD="class: xl65"]49746.1
[/TD]
[TD="class: xl65"]D
[/TD]
[TD="class: xl65"]Williams
[/TD]
[TD="class: xl65"]T
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Occurred
[/TD]
[TD="class: xl65"]Problem Check
[/TD]
[TD="class: xl65"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]LKMN
[/TD]
[TD="class: xl66, bgcolor: transparent"]1/2/2013
[/TD]
[TD="class: xl65, bgcolor: transparent"]49746.1
[/TD]
[TD="class: xl65, bgcolor: transparent"]D
[/TD]
[TD="class: xl65, bgcolor: transparent"]Williams
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]99213
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Office Visit-Est Patient; Intermed
[/TD]
[TD="class: xl65, bgcolor: transparent"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"]49746.1 Count
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]LKMN
[/TD]
[TD="class: xl66, bgcolor: transparent"]1/2/2013
[/TD]
[TD="class: xl65, bgcolor: transparent"]52680.3
[/TD]
[TD="class: xl65, bgcolor: transparent"]E
[/TD]
[TD="class: xl65, bgcolor: transparent"]Roberts
[/TD]
[TD="class: xl65, bgcolor: transparent"]T
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Occurred
[/TD]
[TD="class: xl65, bgcolor: transparent"]Ob Visit
[/TD]
[TD="class: xl65, bgcolor: transparent"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65"]LKMN
[/TD]
[TD="class: xl66"]1/2/2013
[/TD]
[TD="class: xl65"]52680.3
[/TD]
[TD="class: xl65"]E
[/TD]
[TD="class: xl65"]Roberts
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]TH
[/TD]
[TD="class: xl65"]Office Visit-Est Patient; Intermed
[/TD]
[TD="class: xl65"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]52680.3 Count
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65"]LKMN
[/TD]
[TD="class: xl66"]1/2/2013
[/TD]
[TD="class: xl65"]75595.1
[/TD]
[TD="class: xl65"]F
[/TD]
[TD="class: xl65"]Lard
[/TD]
[TD="class: xl65"]T
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Occurred
[/TD]
[TD="class: xl65"]Ob Visit
[/TD]
[TD="class: xl65"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]LKMN
[/TD]
[TD="class: xl66, bgcolor: transparent"]1/2/2013
[/TD]
[TD="class: xl65, bgcolor: transparent"]75595.1
[/TD]
[TD="class: xl65, bgcolor: transparent"]F
[/TD]
[TD="class: xl65, bgcolor: transparent"]Lard
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]99213,TH
[/TD]
[TD="class: xl65, bgcolor: transparent"]TH
[/TD]
[TD="class: xl65, bgcolor: transparent"]Office Visit-Est Patient; Intermed
[/TD]
[TD="class: xl65, bgcolor: transparent"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"]75595.1 Count
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]LKMN
[/TD]
[TD="class: xl66, bgcolor: transparent"]1/2/2013
[/TD]
[TD="class: xl65, bgcolor: transparent"]104405
[/TD]
[TD="class: xl65, bgcolor: transparent"]G
[/TD]
[TD="class: xl65, bgcolor: transparent"]Wilks
[/TD]
[TD="class: xl65, bgcolor: transparent"]T
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Occurred
[/TD]
[TD="class: xl65, bgcolor: transparent"]Ob Visit
[/TD]
[TD="class: xl65, bgcolor: transparent"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65"]LKMN
[/TD]
[TD="class: xl66"]1/2/2013
[/TD]
[TD="class: xl65"]104405
[/TD]
[TD="class: xl65"]G
[/TD]
[TD="class: xl65"]Wilks
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]99213,TH
[/TD]
[TD="class: xl65"]TH
[/TD]
[TD="class: xl65"]Office Visit-Est Patient; Intermed
[/TD]
[TD="class: xl65"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]104405 Count
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65"]LKMN
[/TD]
[TD="class: xl66"]1/2/2013
[/TD]
[TD="class: xl65"]119088
[/TD]
[TD="class: xl65"]J
[/TD]
[TD="class: xl65"]Smith
[/TD]
[TD="class: xl65"]T
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Occurred
[/TD]
[TD="class: xl65"]Ob Visit
[/TD]
[TD="class: xl65"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]LKMN
[/TD]
[TD="class: xl66, bgcolor: transparent"]1/2/2013
[/TD]
[TD="class: xl65, bgcolor: transparent"]119088
[/TD]
[TD="class: xl65, bgcolor: transparent"]J
[/TD]
[TD="class: xl65, bgcolor: transparent"]Smith
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]TH
[/TD]
[TD="class: xl65, bgcolor: transparent"]Office Visit-Est Patient; Intermed
[/TD]
[TD="class: xl65, bgcolor: transparent"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"]119088 Count
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]LKMN
[/TD]
[TD="class: xl66, bgcolor: transparent"]1/2/2013
[/TD]
[TD="class: xl65, bgcolor: transparent"]123931
[/TD]
[TD="class: xl65, bgcolor: transparent"]K
[/TD]
[TD="class: xl65, bgcolor: transparent"]Nolan
[/TD]
[TD="class: xl65, bgcolor: transparent"]T
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Occurred
[/TD]
[TD="class: xl65, bgcolor: transparent"]Ob Visit
[/TD]
[TD="class: xl65, bgcolor: transparent"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65"]LKMN
[/TD]
[TD="class: xl66"]1/2/2013
[/TD]
[TD="class: xl65"]123931
[/TD]
[TD="class: xl65"]K
[/TD]
[TD="class: xl65"]Nolan
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]99213,TH
[/TD]
[TD="class: xl65"]TH
[/TD]
[TD="class: xl65"]Office Visit-Est Patient; Intermed
[/TD]
[TD="class: xl65"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]123931 Count
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65"]LKMN
[/TD]
[TD="class: xl66"]1/2/2013
[/TD]
[TD="class: xl65"]164819
[/TD]
[TD="class: xl65"]L
[/TD]
[TD="class: xl65"]Morris
[/TD]
[TD="class: xl65"]T
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Occurred
[/TD]
[TD="class: xl65"]Ob Visit
[/TD]
[TD="class: xl65"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]LKMN
[/TD]
[TD="class: xl66, bgcolor: transparent"]1/2/2013
[/TD]
[TD="class: xl65, bgcolor: transparent"]164819
[/TD]
[TD="class: xl65, bgcolor: transparent"]L
[/TD]
[TD="class: xl65, bgcolor: transparent"]Morris
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]59400,X
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Routine Obstetric Care Includi Ng a
[/TD]
[TD="class: xl65, bgcolor: transparent"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"]164819 Count
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</TBODY>[/TABLE]


[TABLE="width: 74"]
<TBODY>[TR]
[TD="class: xl65, width: 98, bgcolor: transparent"]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





[TABLE="width: 818"]
<TBODY>[TR]
[TD="class: xl65, width: 92"]Practice Code
[/TD]
[TD="class: xl65, width: 77"]Appt Date
[/TD]
[TD="class: xl65, width: 100"]Pat Person Nbr
[/TD]
[TD="class: xl65, width: 98"]Pat First Name
[/TD]
[TD="class: xl65, width: 95"]Pat Last Name
[/TD]
[TD="class: xl65, width: 78"]Appt Status
[/TD]
[TD="class: xl65, width: 77"]CPT
[/TD]
[TD="class: xl65, width: 111"]Appt Status Desc
[/TD]
[TD="class: xl65, width: 227"]Reason Desc
[/TD]
[TD="class: xl65, width: 135"]Appt Provider Name
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]LKMN
[/TD]
[TD="class: xl66, bgcolor: transparent"]1/2/2013
[/TD]
[TD="class: xl65, bgcolor: transparent"]31064.3
[/TD]
[TD="class: xl65, bgcolor: transparent"]B
[/TD]
[TD="class: xl65, bgcolor: transparent"]Jones
[/TD]
[TD="class: xl65, bgcolor: transparent"]T
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Occurred
[/TD]
[TD="class: xl65, bgcolor: transparent"]Post Op
[/TD]
[TD="class: xl65, bgcolor: transparent"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65"]LKMN
[/TD]
[TD="class: xl66"]1/2/2013
[/TD]
[TD="class: xl65"]31064.3
[/TD]
[TD="class: xl65"]B
[/TD]
[TD="class: xl65"]Jones
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Office Visit-Est Patient; Intermed
[/TD]
[TD="class: xl65"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]31064.3 Count
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65"]LKMN
[/TD]
[TD="class: xl66"]1/2/2013
[/TD]
[TD="class: xl65"]52680.3
[/TD]
[TD="class: xl65"]E
[/TD]
[TD="class: xl65"]Roberts
[/TD]
[TD="class: xl65"]T
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Occurred
[/TD]
[TD="class: xl65"]Ob Visit
[/TD]
[TD="class: xl65"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]LKMN
[/TD]
[TD="class: xl66, bgcolor: transparent"]1/2/2013
[/TD]
[TD="class: xl65, bgcolor: transparent"]52680.3
[/TD]
[TD="class: xl65, bgcolor: transparent"]E
[/TD]
[TD="class: xl65, bgcolor: transparent"]Roberts
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]TH
[/TD]
[TD="class: xl65, bgcolor: transparent"]Office Visit-Est Patient; Intermed
[/TD]
[TD="class: xl65, bgcolor: transparent"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl67"]52680.3 Count
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]LKMN
[/TD]
[TD="class: xl66, bgcolor: transparent"]1/2/2013
[/TD]
[TD="class: xl65, bgcolor: transparent"]119088
[/TD]
[TD="class: xl65, bgcolor: transparent"]J
[/TD]
[TD="class: xl65, bgcolor: transparent"]Smith
[/TD]
[TD="class: xl65, bgcolor: transparent"]T
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Occurred
[/TD]
[TD="class: xl65, bgcolor: transparent"]Ob Visit
[/TD]
[TD="class: xl65, bgcolor: transparent"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65"]LKMN
[/TD]
[TD="class: xl66"]1/2/2013
[/TD]
[TD="class: xl65"]119088
[/TD]
[TD="class: xl65"]J
[/TD]
[TD="class: xl65"]Smith
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]TH
[/TD]
[TD="class: xl65"]Office Visit-Est Patient; Intermed
[/TD]
[TD="class: xl65"]Smith, Doctor
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl67, bgcolor: transparent"]119088 Count
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65"]1
[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]
 
Last edited:
Sort by date Sort by votes
Try this:-
Code: 
[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
 
Upvote 0
Code: 
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
 
Upvote 0
I apologize for the simplicity of this follow up question, but where do I go on my worksheet to insert this code?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
mickg said:
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.
Click to expand...

thank you - this all worked!!!!
 
Upvote 0
In this same example could you help me with the code to remove all of the rows that subtotaled 0 in the CPT column?
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Remove all rows that Subtotal Zero
Replies
8
Views
3K
LoraKnight
L
M
  • Question Question
insert a row with the formulas of the row above it
Replies
1
Views
730
M15tyw00d
M
G
  • Question Question
% of Total without including subtotals
Replies
1
Views
916
zac_fozz
zac_fozz
G
  • Question Question
how not to include subtotals in % of total
Replies
0
Views
463
gabew101
G
L
  • Question Question
adding duplicates the sorting from largest to lowest
Replies
3
Views
2K
scottylad2
scottylad2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top