PDA

View Full Version : [SOLVED] Error: AutoFilter method of Range Class failed



Shaolin
04-15-2005, 12:54 PM
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
04-15-2005, 01:10 PM
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
04-15-2005, 01:19 PM
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
04-15-2005, 01:26 PM
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... :think:

Shaolin
04-15-2005, 01:43 PM
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... :think:

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
04-15-2005, 01:54 PM
Out of curiosity, what version of Excel are you using? 97, 2000, XP, 2003?

Shaolin
04-15-2005, 01:58 PM
I'm using 2003.

Ken Puls
04-15-2005, 02:01 PM
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... :dunno

Shaolin
04-15-2005, 02:14 PM
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
04-15-2005, 02:36 PM
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! :yes

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
04-16-2005, 02:28 AM
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
04-16-2005, 09:22 AM
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
04-16-2005, 01:22 PM
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
04-16-2005, 01:26 PM
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
04-16-2005, 02:17 PM
Kapuls,



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


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. :banghead:

mark007
04-16-2005, 02:24 PM
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
04-16-2005, 02:28 PM
Hi Shaolin,

Try this:


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

Shaolin
04-16-2005, 05:28 PM
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
04-16-2005, 09:06 PM
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
04-17-2005, 12:59 AM
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

Ken Puls
04-17-2005, 09:00 AM
Wow, John!

That's really cool! I didn't know about that one. Shaolin, follow John's route. It means that you don't have to nuke anything at all, so the data stays there. It just doesn't show up. :yes

Shaolin
04-17-2005, 02:01 PM
Yeah Kapuls, that is a slick code by john. The link is intact and the zeros are eliminated.

There seems to be one problem. I linked 32 spreadsheets to the master spreadsheet. When linking, I linked an additional 10 blank rows at the end of each of the 32 spreadsheets as well, so if the company decides to add some data, then the master spreadsheet can incorporate those additions.

However, when I run the following macro, it hides a lot of the rows containing a "Yes" or "Y" in column K.



Sub Testing2()
Application.ScreenUpdating = False
Dim r As Range
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Rows(3).AutoFilter
ws.Rows(3).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
ActiveWindow.DisplayZeros = False
End Sub




To test the macro I linked two excel files testing1.xls (row 3 through row 29) and testing1a.xls (row 3 through row 12) to testing2.xls from row 3- row 39. After running the macro on testing2.xls, it hides rows 3,4,9-11,13,15,17,19,22-41

The good thing is only the rows with "Yes" and "Y" show.

The bad thing is it hides the rows 3,30-32,35, which all containing either a "Yes" or "Y."

I already attached testing1 already (I believe) and here is testing 1a.xls

Shaolin
04-18-2005, 04:16 AM
I think I might have confused some people. The code I posted in the previous post, does it read from the cell itself, or does it read from the formula bar? Reason I ask is cause of the links.

For instance, the forumla bar can contain "='D:\Documents and Settings\My Documents\Manna\Testing Carlitz\[Testing1.xls]Sheet1'!K14," or in the cell it reads "Yes." Does it look directly at the cell, or does it look at the forumla bar itself? i would assume the cell. That would make more sense to me.

Shaolin
04-18-2005, 07:51 AM
After making one slight adjustment, it works. I want to thank everyone. I'm going to barnes and noble to purchase a book on vba to strength my fundamentals and after some practice I should be able to help some newbies that come along. As the saying goes, "what goes around comes around!"

Thanks again everyone!:thumb

Ken Puls
04-18-2005, 08:36 AM
Shaolin, cool! :thumb (On both figuring out your own issue and your desire to help out here!)

So... should we mark this one solved? ;)

Shaolin
04-18-2005, 11:23 AM
yes Kpuls, it's solved.

[dave chappelle]Open and shut case Johnson[/dave chappelle]

Ken Puls
04-18-2005, 12:41 PM
Great. I'll mark it solved, but for next time, see the note in my signature. ;)

Shaolin
04-18-2005, 01:18 PM
Great. I'll mark it solved, but for next time, see the note in my signature. ;)

Oh, now I know!