Error: AutoFilter method of Range Class failed

[Shaolin]

I am throughly confused. Someone help me please!!

Sub Test()
Application.ScreenUpdating = False
Dim r As Range
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Rows(1).AutoFilter
ws.Rows(1).AutoFilter , Field:=11, _
Operator:=xlOr, Criteria1:="=Yes", Criteria2:="=Y"
Set r = Application.Intersect(ws.Cells.SpecialCells(xlCellTypeVisible), _
ws.UsedRange.Offset(1, 0))
r.EntireRow.RowHeight = 12.75
ws.Rows(1).AutoFilter , Field:=11, _
Operator:=xlOr, Criteria1:="<>Yes", Criteria2:="<>Y"
Set r = Application.Intersect(ws.Cells.SpecialCells(xlCellTypeVisible), _
ws.UsedRange.Offset(1, 0))
ws.AutoFilterMode = False
r.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub

[Ken Puls]

Hi Shaolin,

If memory serves, try using ws.range("A1").AutoFilter

I believe it should pick up the entire row for you automatically.

Post back if it doesn't though..

[Shaolin]

Hi Shaolin,

If memory serves, try using ws.range("A1").AutoFilter

I believe it should pick up the entire row for you automatically.

Post back if it doesn't though..

The error is now "Application-defined or object-defined error"

Just to let you know, the code should scan through column K (starting at k3 til the end), and where ever there is not a "Yes" or "Y" the row that cell is on should be hidden and if that row has a yes, it should either remain at standard row height of 12.75 or if it was originally hidden, the row height should be altered to 12.75.

[Ken Puls]

The error is now "Application-defined or object-defined error"

Really?

This works fine for me:

Sub Test()
Application.ScreenUpdating = False
Dim r As Range
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Range("A1").AutoFilter
ws.Range("A1").AutoFilter , Field:=11, _
Operator:=xlOr, Criteria1:="=Yes", Criteria2:="=Y"
Set r = Application.Intersect(ws.Cells.SpecialCells(xlCellTypeVisible), _
ws.UsedRange.Offset(1, 0))
r.EntireRow.RowHeight = 12.75
ws.Range("A1").AutoFilter , Field:=11, _
Operator:=xlOr, Criteria1:="<>Yes", Criteria2:="<>Y"
Set r = Application.Intersect(ws.Cells.SpecialCells(xlCellTypeVisible), _
ws.UsedRange.Offset(1, 0))
ws.AutoFilterMode = False
r.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub

Does exactly what you describe...

[Shaolin]

Really?

This works fine for me:

Sub Test()
Application.ScreenUpdating = False
Dim r As Range
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Range("A1").AutoFilter
ws.Range("A1").AutoFilter , Field:=11, _
Operator:=xlOr, Criteria1:="=Yes", Criteria2:="=Y"
Set r = Application.Intersect(ws.Cells.SpecialCells(xlCellTypeVisible), _
ws.UsedRange.Offset(1, 0))
r.EntireRow.RowHeight = 12.75
ws.Range("A1").AutoFilter , Field:=11, _
Operator:=xlOr, Criteria1:="<>Yes", Criteria2:="<>Y"
Set r = Application.Intersect(ws.Cells.SpecialCells(xlCellTypeVisible), _
ws.UsedRange.Offset(1, 0))
ws.AutoFilterMode = False
r.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub

Does exactly what you describe...

Oh, interesting. Well, now it's mad at line 7 and I went line for line, character for character

ws.Range("A1").AutoFilter

error: AutoFilter method of Range class failed

and yes, I used columns A-K with all columns being used

[Ken Puls]

Out of curiosity, what version of Excel are you using? 97, 2000, XP, 2003?

[Shaolin]

I'm using 2003.

[Ken Puls]

Okay, then that's weird. Excel 97 (I just found out) doesn't like a "," after Autofilter when you're setting up the criteria. But 2003 reads it just fine.

Is there any way you could upload a sample workbook, stripped of anything confidential? I've filled columns A;K with random data, and haven't had any issues with the code at all. If I can get my hands on a sample, maybe I can figure it out for you...

[Shaolin]

It runs now. I ran it on a test spreadsheet. This is what it did. It hid rows 2 through 14 in the test spreadsheet, whereas I want it to start on row 3. The thing is that there are some Yes or Y in the rows that it hid. Have any ideawhat might have happened? Do you want me to still send the spreadsheet?

What is your email address?

[Ken Puls]

Hi there,

Nope, don't need to email it to me. FYI, for future use, you can attach it to your post (must be in zip format). Just click the "Go Advanced" button (or Post reply), and part way down, you'll see a button to "Manage Attachments". That also means that if someone else comes along, they can download it and work on it too!

At any rate, try changing your second autofitler to xlAnd, not xlOr. That'll nail one of them.

Out of curiosity, can you just unhide row 3 afterwards as a workaround? Try:

ws.Rows(3).Hidden = False

[mark007]

Kpuls,

ws.Rows(1).AutoFilter

will ensure the entire row is autofiltered as oppose to Excel guessing how many columns.

Shaolin,

To start at row 3 rather than 2 change both filters to:

ws.Rows(2).AutoFilter

[Shaolin]

Thanks

Sub Testing()
Application.ScreenUpdating = False
Dim r As Range
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Range("A1").AutoFilter
ws.Range("A1").AutoFilter , Field:=11, Operator:=xlOr, Criteria1:="=Yes", Criteria2:="=Y"
Set r = Application.Intersect(ws.Cells.SpecialCells(xlCellTypeVisible), ws.UsedRange.Offset(1, 0))
ws.Rows(3).Hidden = False
r.EntireRow.RowHeight = 12.75
ws.Range("A1").AutoFilter , Field:=11, Operator:=xlOr, Criteria1:="<>Yes", Criteria2:="<>Y"
Set r = Application.Intersect(ws.Cells.SpecialCells(xlCellTypeVisible), ws.UsedRange.Offset(1, 0))
ws.AutoFilterMode = False
r.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub

Mark, I'm going to change the the 3 to a 2, like you suggested and see how it works.

oh, and btw, when I ran the above code lines 2 through 23 were hidden. Only the rows with the cell in column K (which is the 11th column) should not be hidden under the condition there is a "Yes" or "Y."

Kpuls, I added the orange highlighted line. It made the most sense to me to add it there.

I attached a test excel file with a bunch of random stuff except for column K, which has Yes, Y or a blank.

Oh, and btw, if I wanted to change the cells that have a '0' in them to a blank, how do I do that?

[Ken Puls]

ws.Rows(1).AutoFilter

will ensure the entire row is autofiltered as oppose to Excel guessing how many columns.

Hi Mark,

You know, I think I must have second guessed myself at that sometime in the past. Probably when I ran into a debug error on that line. I just assumed that you had to set the range. It always seemed to me that it should work that way!

if I wanted to change the cells that have a '0' in them to a blank, how do I do that?

Hi Shaolin,

One way would be to just use the "replace" feature. Way faster than looping. Change the columns to the ones you want to work on...

Columns(1).Replace What:="0", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

HTH,

[mark007]

Shaolin,

To clarify:

Sub Testing() 
    Application.ScreenUpdating = False 
Dim r As Range 
    Dim ws As Worksheet 
    Set ws = ActiveSheet 
    ws.Rows(2).AutoFilter 
    ws.Rows(2).AutoFilter Field:=11, Operator:=xlOr, Criteria1:="=Yes", Criteria2:="=Y" 
    Set r = Application.Intersect(ws.Cells.SpecialCells(xlCellTypeVisible), ws.UsedRange.Offset(1, 0)) 
   r.EntireRow.RowHeight = 12.75 
ws.Range("A1").AutoFilter Field:=11, Operator:=xlOr, Criteria1:="<>Yes", Criteria2:="<>Y" 
    Set r = Application.Intersect(ws.Cells.SpecialCells(xlCellTypeVisible), ws.UsedRange.Offset(1, 0)) 
    ws.AutoFilterMode = False 
    r.EntireRow.Hidden = True 
Application.ScreenUpdating = True 
End Sub

HTH

[Shaolin]

Kapuls,

[VBA]

Columns(1).Replace What:="0", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

[/VBA]

It only works for column A. I changed Columns(1) to Columns(11) hoping that it will replace "0" with " " and it only worked on column 11. then I changed it to Column(1:11) and the compiler got mad. How can I make it work for columns 1:11 without repeating it 11 times.


Mark,

Does that code work for you? It hides all the rows in that test spreadsheet. go figure.

[mark007]

Ah, I think the operator needs changing for the second one:

Sub Testing() 
    Application.ScreenUpdating = False 
Dim r As Range 
    Dim ws As Worksheet 
    Set ws = ActiveSheet 
    ws.Rows(2).AutoFilter 
    ws.Rows(2).AutoFilter Field:=11, Operator:=xlOr, Criteria1:="=Yes", Criteria2:="=Y" 
    Set r = Application.Intersect(ws.Cells.SpecialCells(xlCellTypeVisible), ws.UsedRange.Offset(1, 0)) 
    r.EntireRow.RowHeight = 12.75 
ws.Range("A1").AutoFilter Field:=11, Operator:=xlAnd, Criteria1:="<>Yes", Criteria2:="<>Y" 
    Set r = Application.Intersect(ws.Cells.SpecialCells(xlCellTypeVisible), ws.UsedRange.Offset(1, 0)) 
    ws.AutoFilterMode = False 
    r.EntireRow.Hidden = True 
Application.ScreenUpdating = True 
End Sub

[Ken Puls]

Hi Shaolin,

Try this:

Columns("A:K").Replace What:="0", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

[Shaolin]

thanks Marc, it actually works.

Kapul, thanks a lot. the code actually replaces the zeros with blanks. However, the actual spreadsheet is linked, so it has data sources. and when a the original spreadsheet has a blank, the spreadsheet that it's paste linked to shows zeros when infact it should be blanked. For the past hour I've been trying to figure out a way replace the zeros with blanks under those circumstances, is it even possible?

[Ken Puls]

Hi Shaolin,

One approach would be to convert all the formulas to values, then run the search and replace. Of course, this only works if you don't need the links to the other workbook to remain intact. I'm assuming that since you want to replace those with blanks, though, that this isn't important.

Throw this is before the replace code I gave you above:

With Range("A2:K" & Range("K65536").End(xlUp).Row)
    .Cells.Value = .Cells.Value
End With

Save your workbook before you run it, though. That way if anything goes wrong, you can just toss it without saving.

If you do need to keep the rest of the linked formulas intact, we may have to run a search with SpecialCells, looking for formulas. We'd then loop through each, and if the value was 0, replace it. This would be slower though, which is why I'm suggesting the code above.

Cheeers,

[johnske]

Hi Shaolin,

Try slipping this in your code somewhere (maybe at the end, or you can put it on its' own as a WorkSheet_Activate event)

ActiveWindow.DisplayZeros = False

Regards,
John