Select range for autofilter on current worksheet by using input box

0

Dear Sirs,

I have the following macro to add filter on current_worksheet at predefined range provided by Don at the following link https://www.teachexcel.com/talk/2950/add-filters-on-selected-sheets-at-once#topic_answer_section_anchor.

The macro it self is working perfectly. In this macro the range already defined as below

Set Rng = Range(.Cells(3, 1), .Cells(3, .UsedRange.Columns.Count))

I want to have the modified macro that defined the range based on an input box (Default range Range(.Cells(3, 1), .Cells(3, .UsedRange.Columns.Count))

Looking forward to having your further advice in this regards

Best regards

Arsil Hadjar

Sub AddFilter_on_Current_Worksheet()
    Dim Sh As Worksheet
    Dim Rng As Range

    Application.ScreenUpdating = False          ' stop screen flicker

    Set Sh = ActiveSheet

    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
    Application.ScreenUpdating = True
End Sub
[/CODE]
Answer
Discuss

Answers

0
Selected Answer

Hello Arsil,

Here is your modified code. Note that Don's code and mine aren't identical. Rather than modifying Don's effort I hae chosen to modify my own previous procedure. Basically there are two changes.

  1. The default is now variable, depending upon the used range.
  2. The code will work only on the ActiveSheet and is therefore much shorter.
Sub Filter_Current_Worksheet()
    ' 27 Aug 2018
    
    Dim Dflt As String
    Dim Ask As String
    Dim Rng As Range
    
    With ActiveSheet
        Dflt = Range(.Cells(3, 1), Cells(3, .UsedRange.Columns.Count)).Address(0, 0)
        Do
            Ask = InputBox("Enter the address of the captions" & vbCr & _
                           "of the range to filter.", "Apply filter", Dflt)
            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
    
        If .AutoFilterMode Then .AutoFilterMode = False
        Rng.AutoFilter
        .Range("J4").Select                         ' defining pane freeze
    End With
    
    With ActiveWindow
        If .FreezePanes Then .FreezePanes = False
        .FreezePanes = True
    End With
End Sub

The version of the code below just asks for the row number, selecting the filter columns automatically. Observe that the row to freeze panes is set relative to the row entered by the user.

Sub Filter_Current_Worksheet2()
    ' 30 Aug 2018
    
    Dim Ask As String, R As Long
    Dim Rng As Range
    
    With ActiveSheet
        Do
            Ask = InputBox("Enter the row number" & vbCr & _
                           "of the range to filter.", "Apply filter", 3)
            If Trim(Ask) = vbNullString Then Exit Sub
            R = Val(Ask)
            On Error Resume Next
            Set Rng = Range(.Cells(R, 1), Cells(R, .UsedRange.Columns.Count))
        Loop While Rng Is Nothing
    
        If .AutoFilterMode Then .AutoFilterMode = False
        Rng.AutoFilter
        .Cells(R + 1, "J").Select                    ' defining pane freeze
    End With
    With ActiveWindow
        If .FreezePanes Then .FreezePanes = False
        .FreezePanes = True
    End With
End Sub
Discuss

Discussion

Dear Variatus,
Many thanks for the modified code.
It works perfectly
Best regards
Arsil Hadjar
Arsil (rep: 28) Aug 27, '18 at 8:14 pm
Dear Variatus,
Just curious to know on whether there is a possibility to input only the row number for the range and the colums range defined by the macro code below
Set Rng = Range(.Cells(3, 1), .Cells(3, .UsedRange.Columns.Count))
- Rows number 3 ( from input box)
- Colums range up to ..UsedRange.Columns.Count)

Looking forward to receiving your advice in this regards

Best regards
Arsil Hadjar

 
Arsil (rep: 28) Aug 29, '18 at 7:50 am
Dear Variatus,
Many thanks for the version of the code that just asks for the row number, selecting the filter columns automatically.
It works perfectly

Best regards
Arsil Hadjar

Arsil (rep: 28) Aug 30, '18 at 7:55 pm
Add to Discussion

Answer the Question

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