AUTOFILTERS in VBA

Question

I thought I was finally done with my workbook but alas there arose an issue when we put it into practice (much to my dismay).

In short; I was using autofilter (not in vba) menus to filter the items in my warehouse, and the shelves they were on. When I filtered out everything but the shelves i wanted to add inventory to, the values I added (through a VBA programmed button that basically copies everything in the "add to stock" (AKA "C4:C1000" row and adds it into the "currently in stock" row (AKA "D1:d1000")) got added to the wrong row.

My solution was to use the autofilters to find the correct shelf, write the amount added to the stock, and then press the button. The VBA code of the button would (in my new plan) then do exactly as before, only this time it would first remove the filters, execute as before, and then re-apply filters.

I cannot - for the life of me - figure out how to turn the autofilters back on with VBA code though.

I have searched far and wide on the net, but the closest I can find to what I want is the following code:

Activesheet.range("a4").autofilter

That does nothing but stop my code from completing its execution mid-way.

Please help!

The full code for one of the pages is as follows:

Sub AddtoInnTotalandclear() 'The macro is used to move all amounts plottet into "INN" colum. Amounts are moved into "TOTAL AMOUNT IN STORAGE" while clearing the "INN" colum simultaneously

'Removes flickering from the screen (part 1 of 2)

Application.ScreenUpdating = False

'removes the protection on the worksheet (NB! you have to have the current password in the code line for this to work)

ActiveSheet.Unprotect Password:="kirk"

'Copies the values from the "inn" colum

Range("c4:c1000").Copy

'Adds the copied values to the values already in the "Total Amount" colum

Range("d4:d1000").PasteSpecial Operation:=xlPasteSpecialOperationAdd

'Clears the "inn" colum

Range("c4:c1000").ClearContents

'Disable marchiing atnsa around copied range

Application.CutCopyMode = False

Range("d4:d1000").Select

Selection.Locked = True

'Allows autofilter usage despite the document being locked '& 'Re-Activates the password protection

With ActiveSheet

.Protect Password:="kirk", AllowFiltering:=True

.EnableSelection = xlNoRestrictions

End With

'Determines where you end up when you are finished

Worksheets("in").Range("c4").Select

'Removes flickering from the screen (part 2 of 2)

Application.ScreenUpdating = True

End Sub

---- I want to remove/disable autofilters when I press the button that activates this VBA code, then re-activate the autofilters once the entire procedure is done...

I need an EXCEL JEDI to give me some sound code advice here.. Please :)

It is unclear what you did. Did you remove AutoFilter from the worksheet completely or did you just ShowAllData on all filtered columns?? — Gary's Student
I went into VBA and added the following code to the sub. Activesheet.autofiltermode=false — John123456789
If you're having a problem with your code then it's always a good idea to include it in your question. — Tim Williams

Gary's Student Gary's Student · Accepted Answer · 2020-12-18T18:22:17

Here is a demo example. Say we have data in cols A through D with the headers in row#1. If you run:

Sub qwerty()
    Dim s As Worksheet
    Set s = ActiveSheet
    
    s.AutoFilterMode = False
    s.Range("A:D").AutoFilter
End Sub

You will end up with filtering on cols A through D, but with no criteria on any of those columns applied.

EDIT#1:

If your header row is row #3 and we are filtering cols A through D, then:

Sub qwerty2()
    Dim s As Worksheet, N As Long
    Set s = ActiveSheet
    
    N = Cells(Rows.Count, "A").End(xlUp).Row
    s.AutoFilterMode = False
    s.Range("A3:D" & N).AutoFilter
End Sub

AUTOFILTERS in VBA

The full code for one of the pages is as follows:

1 Answers