none
Filter Ascending only on the active cell. RRS feed

  • Question

  • Hello Guys,

    I was working on a macro to 

    Step:-

    1) Unprotect Worksheet

    2) Autofilter the range and then filter ascending on the active cell.

    3) Protect the worksheet again

    here is the code :-

    Sub Filter_ActiveCell()

        Dim Acell As String
            
        Acell = ActiveCell
            
            ActiveSheet.Unprotect Password:="bismillah1111"
            Rows("3:3").Select
            Selection.AutoFilter
            
    ' Here is where the problem lies.                
        ActiveWorkbook.Worksheets("Summary Sheet ").AutoFilter.Sort.SortFields.Add Key _
            :=Range(Acell), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
            
        With ActiveWorkbook.Worksheets("Summary Sheet ").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
          
        Selection.AutoFilter
             
        ActiveSheet.Protect Password:="bismillah1111"
               
    End Sub

    I am trying hard to understand but the variable doesn't work. this took 2 hours of mine but all in vain.

    Can someone please help me in this.

    Thanks,

    Wasimali Bori

    Thursday, February 19, 2015 3:56 PM

Answers

  • The line

    Acell = ActiveCell

    will set the variable Acell to the value of the active cell. One way to do what you want would be to set Acell to the address of the active cell:

    Acell = ActiveCell.Address

    Another way would be

    Sub Filter_ActiveCell()
        Dim Acell As Range
        Set Acell = ActiveCell

        ActiveSheet.Unprotect Password:="bismillah1111"
        Rows("3:3").Select
        Selection.AutoFilter

        ' Here is where the problem lies.
        ActiveSheet.AutoFilter.Sort.SortFields.Add Key _
            :=Acell, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal

        With ActiveSheet.AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

        Selection.AutoFilter

        ActiveSheet.Protect Password:="bismillah1111"
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Wasimali Bori Friday, February 20, 2015 7:28 AM
    Thursday, February 19, 2015 8:14 PM

All replies

  • The line

    Acell = ActiveCell

    will set the variable Acell to the value of the active cell. One way to do what you want would be to set Acell to the address of the active cell:

    Acell = ActiveCell.Address

    Another way would be

    Sub Filter_ActiveCell()
        Dim Acell As Range
        Set Acell = ActiveCell

        ActiveSheet.Unprotect Password:="bismillah1111"
        Rows("3:3").Select
        Selection.AutoFilter

        ' Here is where the problem lies.
        ActiveSheet.AutoFilter.Sort.SortFields.Add Key _
            :=Acell, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal

        With ActiveSheet.AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

        Selection.AutoFilter

        ActiveSheet.Protect Password:="bismillah1111"
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Wasimali Bori Friday, February 20, 2015 7:28 AM
    Thursday, February 19, 2015 8:14 PM
  • Thank you Hans!!

    I guess you are the only one running the VBA forums.... lol... Thanks again sir!!!

    Can you help me in this.

    The macro should should run only when any cell from Rows("3:3") is selected.

    If any other cell is selected we can have a MSG box and end the macro. 

    Friday, February 20, 2015 7:33 AM
  • I wouldn't display a message box each time a cell in another row is selected. That would become irritating after a while.

    Right-click the sheet tab of the relevant worksheet.

    Select View Code from the context menu.

    Copy the following code into the worksheet module:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Not Intersect(Range("3:3"), Target) Is Nothing Then
            If Intersect(Range("3:3"), Target).Count = 1 Then
                Application.EnableEvents = False
                Call Filter_ActiveCell
                Application.EnableEvents = True
            End If
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, February 20, 2015 11:27 AM