Delete total rows with auto filter 1

[ColdDay]

I looked thru past posts and did not find anything that matched my issue. I want to delete the rows with the word "Total" after doing a subtotal. I have done a copy and paste special for my subtotals so I can work on them. I have a few notes in the code below.

Sub DeleteRowsWithWordTotal()
Dim lLastRow As Long  'last row in range
Dim rng As Range
Dim rngDelete As Range

'freeze screen
Application.ScreenUpdating = False

Sheets("WordTotalSheet").Select
With ActiveSheet
'reset last cell. Might look empty, but formatting is read as something in cell
.UsedRange

'determine last row
lLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

'set rng to col A that has the word or whatever to delete
Set rng = Range("A1", Cells(lLastRow, "A"))

'-------------------------------
'problem is below
'following is to filter the A col to show only data/rows to be deleted

'no error with following. but it filters out everything and no rows are deleted.
rng.AutoFilter field:=1, Criteria1:=Right(ActiveCell, 5) = "Total"

'tried variations of the following with the asterisk with no success
'rng.AutoFilter field:=1, Criteria1:=Right(ActiveCell, 5) = "=*Total*"

'following gives Type Mismatch error
'rng.AutoFilter field:=1, Criteria1:=CStr(Right(rng.Cells, 5)) = "Total"

'following gives Out of Memory error
'rng.AutoFilter field:=1, Criteria1:=CStr(Right(Cells, 5)) = "Total"
'------------------------------

'gets reference to the visible cells, including headers in row 1
'the value in rng has been set above to include A1. W/o the .Offset below _
    the header in A1 would be deleted. With the .Offset, it is not.
Set rngDelete = rng.Offset(1, 0).SpecialCells(xlCellTypeVisible)

'turn off AutoFilter
rng.AutoFilter

'delete rows with word "Total"
rngDelete.EntireRow.Delete

'reset last cell
.UsedRange

End With
End Sub

What am I not doing correctly?

Thanks.

 

[SkipVought]

Hi,

... after doing a subtotal

If you used the SUBTOTAL Wizard, then simply remove the sub totals using the Wizard.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PHV]

What about this ?
rng.AutoFilter field:=1, Criteria1:="=Total*"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ColdDay]

After reading and thinking about Skip's reply, I realized that in my haste to post, I got it backwards. I want to delete the rows that do not have the word "Total". Hence, leaving the totals and deleting the detail. Sorry for the backwardness. Anyway, I tried PHV's suggestion except with <> instead of =. Still no luck. Some code and results are below.

'following does nothing to any data. no error.  w/asterisk
rng.AutoFilter field:=1, Criteria1:=Right(ActiveCell, 5) = "<>Total*"

'following deletes all rows of data.
'rng.AutoFilter field:=1, Criteria1:="<>Total*"


'following does nothing to data. no error. no asterisk.
'rng.AutoFilter field:=1, Criteria1:=Right(ActiveCell, 5) = "<>Total"

Sorry and thanks again.

 

[ColdDay]

If I am filtering and have a resulting set of data, would there be an "ActiveCell"?

 

[PHV]

And this ?
rng.AutoFilter field:=1, Criteria1:="<>*Total*"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ColdDay]

Got it!

I've been using a .Offset and looping thru the lines looking for the cells without the word "Total".

This little bit of code will speed up a currently long process that I use with a couple of procedures.

Thanks PHV!

Bill

 

[ColdDay]

Just curious. Why does the following give an "Out of memory" error? Is it reading every cell in column A? I wouldn't think that that would be an issue.

rng.AutoFilter field:=1, Criteria1:=CStr(Right(Cells, 5)) = "Total

 

[PHV]

I don't think you can use any expression before the operator (=, <>, ..)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886