Auto reapply Autofilter

**Loobrush** · 03-11-2013, 10:23 AM

Hell everyone. So I'm making a spreadsheet for work that record...well a bunch of stuff, that's not really important. Anyway I've set up an autofilter that hides an entire row if it's first cell (i.e. the detail no) is blank. I can get it to work quite easily in excel 2010 using any basic macro, but the minute it's put on the work computer, which uses Excel 2003, I get runtime error 438 which I'm assuming means the macro is not compatible. Unfortunately I'm not very macro savvy, so any huge macros I've managed to find online I haven't been able to work for myself. Also I need to protect the document, so it needs to work along with that. I've attached my file of the document, any help would be appreciated. Thanks.

TEST.xls

**aydeegee** · 03-12-2013, 10:19 AM

The obvious answer is to update the work computer to Excel 2010 ;>)

If it is just the "hide" part of the macro that is failing, perhaps you can record your own macro on the work computer to hide rows and check the syntax. You ciould then amend the macro accordingly (bearing in mind that the macro may then not work on your computer).

I'm sorry, but not having access to Excel 2003, I cannot be of more assistance.

Regards

Alastair

**HaHoBe** · 03-12-2013, 11:17 AM

Hi, Loobrush

why

Hell

Excel prior to 2007 doesn´t know about

    ActiveSheet.AutoFilter.ApplyFilter

To apply a filter in 2003 you should use something like

ActiveSheet.Range("A1:D1").Autofilter Filed:=2, Criteria1:="AC/DC"

to apply the Autofilter and set the Criteria on Column B for the Bandname.

To switch the Autofilter off you may either use

ActiveSheet.AutoFilterMode = False

or

ActiveSheet.ShowAllData

Ciao,
Holger

**Loobrush** · 03-14-2013, 11:26 AM

Originally Posted by aydeegee

The obvious answer is to update the work computer to Excel 2010 ;>)

If it is just the "hide" part of the macro that is failing, perhaps you can record your own macro on the work computer to hide rows and check the syntax. You ciould then amend the macro accordingly (bearing in mind that the macro may then not work on your computer).

I'm sorry, but not having access to Excel 2003, I cannot be of more assistance.

Regards

Alastair

Yea, that was the first thing I tried, he doesn't want to go for it, upgrading costs money and all that. I did offer up a solution to record a macro for auto refresh, but then that would require the use of a button to trigger it and he doesn't want that, he wants it automatic.

Originally Posted by HaHoBe

why

Sorry about that, I was raised by wolves so I'm not very good with people, I probably should have began with 'hello' instead. xP

Originally Posted by HaHoBe

To apply a filter in 2003 you should use something like

ActiveSheet.Range("A1:D1").Autofilter Filed:=2, Criteria1:="AC/DC"

to apply the Autofilter and set the Criteria on Column B for the Bandname.

Thanks, I tried that code, but now I'm getting runtime error 1004; application defined or object error?

**HaHoBe** · 03-14-2013, 12:20 PM

Hi, Loobrush,

no problem as long as we have something we may howl to together (oh no, not the moon

).

Maybe try this code which didn´t bring up the run-time error on testing in your sample workbook:

Sub Loobrush()
With ActiveSheet
  .AutoFilterMode = False
  With .Range("A1:A" & .Cells(Rows.Count, "B").End(xlUp).Row)
    .AutoFilter
    .AutoFilter Field:=1, Criteria1:="<>"
  End With
End With
End Sub

On Sheets Design, Fitting, and PartMark the Autofilter is set up to start on Row 2 while in the other sheets it will start in Row 1.

HTH,
Holger

**Loobrush** · 03-17-2013, 12:24 PM

Originally Posted by HaHoBe

Hi, Loobrush,

no problem as long as we have something we may howl to together (oh no, not the moon

).

Maybe try this code which didn´t bring up the run-time error on testing in your sample workbook:

Sub Loobrush()
With ActiveSheet
  .AutoFilterMode = False
  With .Range("A1:A" & .Cells(Rows.Count, "B").End(xlUp).Row)
    .AutoFilter
    .AutoFilter Field:=1, Criteria1:="<>"
  End With
End With
End Sub

On Sheets Design, Fitting, and PartMark the Autofilter is set up to start on Row 2 while in the other sheets it will start in Row 1.

HTH,
Holger

I think we're getting there slowly. The issue is that the way the sheet works is that placing a tick in any of the checkboxes in 'Detail no.' will refresh the corresponding sheets autofilter, so is there something I can use in place of 'activesheet' as the sheet itself is not going to be active when i want it refreshed?

[Edit] Also I'm not entirely sure what happened but it sort of deleted the rows it was filtering aswell for some reason. Not sure why. :/

**Loobrush** · 03-17-2013, 12:55 PM

Sorry about that.

At any rate that seems to have worked perfectly on the planning sheet, how would I go about amending that to work for all of them?

**HaHoBe** · 03-17-2013, 01:12 PM

Hi, Loobrush,

to my opinion re-think the layout of the workbook. You choose Form-controls for each option - I might have taken an UserForm from VBA with a Listbox (for the names of the sheets) and MultiSelect 2-frmMultiSelectExtended for an array of choices or a ComboBox.

If you want to stay with your form-controls (I think you selected the cells in row 2 for the sheet names) maybe change the code to read

With Worksheets(ActiveCell.Value)

and call the changed macro from within every macro you have created so far. Please note that the cell values have to be equal to the sheetnames (I tested with I2 and a run time error was raised as the cell shows 'Manual Milling' while the sheet is called 'ManualMilling').

Ciao,
Holger

**Loobrush** · 03-17-2013, 01:55 PM

In hindsight that might have been the better option, but I didn't think that far ahead when I was making it, I wasn't even aware I was going to be using any complicated macros. :P

I'll give that code a go and see if it works, else it seems like I'll have to resort to using a button, my employers worst enemy, but that's how it'll have to be. Thanks for all your help Holger.

Ciao.

Auto reapply Autofilter

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Auto reapply Autofilter

Re: Auto reapply Autofilter

Re: Auto reapply Autofilter

Re: Auto reapply Autofilter

Re: Auto reapply Autofilter

Re: Auto reapply Autofilter

Re: Auto reapply Autofilter

Re: Auto reapply Autofilter

Re: Auto reapply Autofilter

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions