CODEWITHSUNDEEP
excelvbaautofilter
John123456789
John123456789

Reputation: 13

Autofilters in VBA

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.

How do I turn the autofilters back on with VBA?

The closest I can find to what I want is the following:

Activesheet.range("a4").autofilter

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

The full code for one of the pages:

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.

Upvotes: 0

Views: 3749

Answers (1)

Gary's Student
Gary's Student

Reputation: 96791

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

Upvotes: 1

Related Questions