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.
What am I not doing correctly?
Thanks.
Code:
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.