Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Select range or row for adding Autofilter for all worksheets by inputbox

0

Dear Sirs,

I have the following macro suggested by Variatus to add filter for all worksheets 

Refer to link below on 28 May 2018 

'https://www.teachexcel.com/talk/2740/add-filters-on-all-worksheets#topic_answer_section_anchor

I need the help in modifying macro to select the range for Autofilter by inputbox with the default of Rows("3:3")

Looking forward to having your further advice in this regards

Best regards

Arsil Hadjar

Sub AddFilter_on_allWorksheet()
    ' Hello Arsil,
    'The AutoFilter method is a toggle.
    'Every time it is called it will change to the opposite of what it was.
    'To avoid surprises use the AutoFilterMode to determine the current status.

    Dim Sh As Worksheet
    Dim Rng As Range
    Application.ScreenUpdating = False          ' stop screen flicker
    For Each Sh In Worksheets
        With Sh
            If .AutoFilterMode Then .AutoFilterMode = False
            Set Rng = Range(.Cells(3, 1), .Cells(3, .UsedRange.Columns.Count))
            Rng.AutoFilter
            .Activate
            .Range("J4").Select
        End With
        With ActiveWindow
            If .FreezePanes Then .FreezePanes = False
            .FreezePanes = True
        End With
    Next Sh
    Application.ScreenUpdating = True
End Sub
Answer
Discuss

Answers

0
Selected Answer

This code ought to do the job. Please try it.

Sub AddFilter_on_allWorksheet()
    ' 25 Aug 2018
    
    Dim Ask As String, Rng As Range
    Dim Sh As Worksheet
    Dim FltRng As Range
    
    Do
        Ask = InputBox("Enter the address of the captions" & vbCr & _
                       "of the range to filter.", "Apply filter", "A3:J3")
        If Trim(Ask) = vbNullString Then Exit Sub
        On Error Resume Next
        Set Rng = Range(Ask).Rows(1)            ' correct wrong entry: 1 row only
    Loop While Rng Is Nothing
    
    Application.ScreenUpdating = False          ' stop screen flicker
    For Each Sh In Worksheets
        With Sh
            If .AutoFilterMode Then .AutoFilterMode = False
            Set FltRng = .Range(Rng.Address)
            FltRng.AutoFilter
            .Activate
            .Range("J4").Select
        End With
        With ActiveWindow
            If .FreezePanes Then .FreezePanes = False
            .FreezePanes = True
        End With
    Next Sh
    Application.ScreenUpdating = True
End Sub

I point out that range "3:3" applies the filter to 16,384 columns. Excel will not complain but the understanding is that you know what you are asking.

Discuss

Discussion

Dear Variatus,

Many thanks  for the modified macros. It works .

If I want to run it only for the current worksheet , what is the  solution for this

Best regards
Arsil Hadjar
Arsil (rep: 32) Aug 26, '18 at 9:44 pm
Hello Arsil,
Your question was answered as asked. Had you thought of your follow-up when you asked your question you would now already have the answer. As it is, you should now accept the answer and present your next question in a new thread.
Regards!
Variatus (rep: 4889) Aug 26, '18 at 10:28 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login