Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Delete total rows with auto filter 1

Status
Not open for further replies.

ColdDay

Technical User
Nov 30, 2010
92
US
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.
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.
 
Hi,

... after doing a subtotal

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

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
What about this ?
rng.AutoFilter field:=1, Criteria1:="=Total*"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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.

Code:
'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.
 
If I am filtering and have a resulting set of data, would there be an "ActiveCell"?
 
And this ?
rng.AutoFilter field:=1, Criteria1:="<>*Total*"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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
 
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
 
I don't think you can use any expression before the operator (=, <>, ..)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top