I’ve searched and haven’t found anything that will work yet. I was hoping someone might be able to please
help me. In Excel 2007 (Vista) I have templates(individual worksheets) for recording details of our purchases for each
shopping trip. In these templates the purchases are broken down into various categories (each category has about 15 rows
where data can be entered). To enter the data, the item is entered in Col H, if necessary the quantity is changed from “1”
in col K the item individual cost is entered in Col L. Col M has formula multiplying cost (col L) times quantity (col)K, for
the line total for that item(s). Then in the row below each category group, there is a category subtotal in Col “O”. The
entire template is between 500 and 600 Rows depending on the store. Most rows have pre-labeled items for things we routinely
purchase, to minimize the entry time.
F G H I J K L M N O
Cat 2 SubCat2 Item Brand Size Qty Cost Total Tax Sub tot
Groceries Dairy Skim Milk Kroger 1/2 gal 1 $0.00
Groceries Dairy 2% Milk Kroger 1/2 gal 1 1.5 $1.50
If there isn’t an entry for an item in a particular category, then no item cost is entered in Col L
(leaving the cell blank) and the item Total in col M will show “$0.00”.
At the completion of entering this shopping trip , I copy the data from the template into a history spreadsheet. Which is
just each template (grouped) , copy and pasted below the previous shopping trip.
Each completed shopping trip will have many rows where the item totals are zero. Because there was no entry for that item.
So in the history worksheet, there are a lot of rows that are just taking up space.
I am wanting a macro where I can delete the rows where column M is equal to $0.00
Below are two codes I tried
modifying from other forum responses. But the first code ended up with an error trying to run after it deleted rows in the
bottom most shopping trip on the history spreadsheet. The error said "Run Time error 13, Type Mismatch"
The first and the 2nd one below, both deleted rows where cells in column M were blank. Not just the ones that indicated
$0.00. When I tried to use “$0.00” in the code I received error, guessing the $ sign has special meaning.
what I need is code that will delete the row if Col M equals $0.00
But do not delete the Row if Col M is just blank
If I can use this macro while its still on the template page
before copy the purchases onto the history worksheet, it may take less time. I noted the first code seemed to loop from the
bottom up, and my history page now has around 5000 rows (many of which can be deleted).
If I the code can be written for deleting rows on the template entry page, then I need the code to only delete rows between
Row 41 and 585. Rows above 41 have drop down lists built for use in the various categories etc. I can just adjust my reset
macro to include copying and paste a new template in place.
Sorry I’ve been so wordy, i'm a gray haired rookie.
1st code tried
Dim rng As Range, rng2 As Range
Set rng = Range("M65536").End(xlUp)
Do Until rng.Row = 1
Set rng2 = rng.Offset(-1)
If rng = 0 Then rng.EntireRow.Delete
Set rng = rng2
2nd Code tried
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = LR To 1 Step -1
If Range("M" & i).Value = 0 Then Rows(i).EntireRow.Delete
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic