Run time error 1004-Auto filter

**jad70** · 01-12-2009, 08:28 AM

Hi again,

Well ,another small but annoying problem with my data.
I have 4 command buttons (A-D) so that the users can update after inputting new or revised Data.
My problem is that if updated in order, button D Macro below stops at the first "Selection.AutoFilter..." as if it cannot turn on the Auto Filter.

It will run if I manually set the AutoFilter on the sheet.

I think, and as I am completely new to this please feel free to correct me, the reason that it stops is that on the Button B is a "AdvancedFilter" Macro.
I have to present this on Wednesday the 14th so any help provided will be gratefully recieved.

Please Login or Register  to view this content.

I have looked at the other post regarding this error but my list is correctly formatted.

Many thanks in advance.

**royUK** · 01-12-2009, 08:33 AM

Recording macros is fine, but you must remember the code generated is not very efficient and is best edited. For example you don't need to select ranges.

You can check if a sheet is autofiltered

Please Login or Register  to view this content.

**jad70** · 01-12-2009, 08:41 AM

Hi Roy,

I have tidied up a lot of this (and all the other) macros from using this forum, a great source of info for a novice like myself.
Many thanks for you tip, but a couple of questions:
When you say the I do not have to select ranges, what do you mean? sorry if this sounds a bit stupid but I just want to make sure of what you mean before I alter anything, and
Where would this line of code you gave me go? Again sorry for sounding a bit stupid but still learning.

John.

**jad70** · 01-12-2009, 10:31 AM

Roy,

Answered my own question buy trial and error, many thanks for the code. The macros all run now.

Still, how do I clean up the original macro to avoid selecting ranges etc?

John.

**royUK** · 01-12-2009, 10:41 AM

This is only for guidance

Please Login or Register  to view this content.

Generally it is unnecessary to select a range, sheet or workbook in VBA

**jad70** · 01-12-2009, 11:49 AM

Roy,

Many thanks. Every day is a learning day......

**jad70** · 01-12-2009, 12:11 PM

Roy,

so, in the process of 'cleaning up' this code, and now when I run it I get a Compile error- Expected End With. So I change it and the get a -Expected End Sub. I am still only about a third through getting rid of the selects and using 'with'. Will this problem dissappear when I have finished?

**nigelog** · 01-12-2009, 12:23 PM

Jad
if you get expected "end with" or expected "end sub" errors, all it means is gthat they are not paired. If you use a With you have to use an End With before moving on to the next.

**royUK** · 01-12-2009, 12:31 PM

I've edited the example

**jad70** · 01-12-2009, 12:45 PM

Nigelog,

I think I might be tying myself up in knots here. I am still getting the error and have tried to 'end With' where I see it, but I cannot see the problem.

Please Login or Register  to view this content.

End Sub

Could it be that I am tired? Any help would be great.
Roy, just read you edit and put it in but now getting End If without block If.....aaaarrrgggghhh.
So, off home now so I will catch up tomorrow. Many thanks again for your help guys.

Run time error 1004-Auto filter

LinkBack

Thread Tools

Rate This Thread

Display

Run time error 1004-Auto filter

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions