How to Autofilter on the second row

[dreen]

I have 3 sheets in my workbook, and my code checks if the Active Cell in Sheet 2 is blank or not,

If Len(ActiveCell.Value) = 0 Then
MsgBox "Blank Key in:" & ActiveCell.Address, vbCritical
Exit Sub
End If

If it's not blank, then my code counts the number of times the Active Cell in Sheet 2 appears in Sheet 3 (if any), and if it's greater than 2 times, then a Msgbox box appears asking the user to see the "Previous Entries" which essentially Auto filters Sheet 3 to the Active Cell value from Sheet 2. Here is my entire code:

Option Explicit
Sub Autofilter_Macro4()

 Application.ScreenUpdating = False
 
    Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet                       'Declares variables as worksheets
    Dim rng As Range                                                               'Declares variable as a range to store values
    
    Set sh1 = Sheet1                                                               'Assigns a worksheet to the declared worksheet variable (sh1 = "Main Database" Worksheet = Machine Inv #)
    Set sh2 = Sheet2                                                               'Assigns a worksheet to the declared worksheet variable (sh 2 = "Changes" Worksheet)
    Set sh3 = Sheet3                                                               'Assigns a worksheet to the declared worksheet variable (sh 3 = "Historical Parameters" Worksheet)
    
    Dim rowAC As Long, rowCut As Long                                              'Declares variable and assigns it as a Long data type

    rowAC = ActiveCell.Row                                                         'Sets the Long variable as the Active Cell Row in Sheet 2
    
    If Len(ActiveCell.Value) = 0 Then                                              'Tests if the Active Cell in column A (Key) of the "Changes" Worksheet is blank or not
        
        MsgBox "Blank Key in:" & ActiveCell.Address, vbCritical                    'If the Active Cell is blank, then this MsgBox notifies you that it's blank
        Exit Sub                                                                   'Ends the entire Macro if the Active Cell is Blank
    
    End If                                                                         'Doesn't initiate the MsgBox and continues the Macro if the Key in Column A is not blank
    Dim Source As Range

    Set Source = sh3.Range("A1", sh3.Range("A" & rows.Count).End(xlUp))                                 'Initializing "Source" variable range to last row in Sheet 3

    Dim Counter As Long
    Dim Result As String
    
    sh3.AutoFilterMode = False                                                                          'Clears any Autofilters (if any) in Sheet 3

    Counter = Application.WorksheetFunction.CountIf(Source, sh2.Range("A" & rowAC))                     'Counts # of times the ActiveCell is in the Source range
    
    If Counter > 2 Then                                                                                 'If there are more than 3 duplicates then display a message box
    
    Result = MsgBox("No. of Duplicates in the Historical Parameters Sheet is : " & Counter & vbNewLine & _
    "Do you want to see the Previous Key Entries?", vbYesNo + vbInformation, "Duplicate Key Entries")   'Msgbox displaying the number of duplicate values in Sheet 3

        If Result = vbYes Then
        
            MsgBox "Yes"
        
            sh3.Range("A:A").Autofilter Field:=1, Criteria1:=ActiveCell.Value                            'Autofilters Sheet 3 for the Active Cell (Key) from Sheet 2 ("Changes" Worksheet)
        
            sh3.Range("A2").Value = sh2.Range("MyRange").Value                                           'Sets the Value of Cell "A2" in Sheet 3 to the named range "MyRange" (ActiveCell) from Sheet 1
        
            sh3.Activate                                                                                 'Sets Sheet 3 as the active sheet
        
            ActiveWindow.FreezePanes = True                                                              'Attemps to Freezes the Panes (Top 2 rows) of Sheet 3
    
         Else:
        
            MsgBox "No"
        
         End If
    
    End If
    
Application.ScreenUpdating = True

End Sub

Currently I am trying to freeze the top two panes in Sheet 3 with my Code but the second row is still being included in the Auto Filter.

I would like to either Freeze the panes of the top 2 rows in Sheet 3, or Autofilter from the Second row (so excluding the Header row 1 and the sub header Row 2) or perhaps another simpler code/solution. Thanks for your help everyone.

Cross-posted on another platform as well: How to Autofilter on the second row

 

[dreen]

Solved, the solution was to include :

'To filter from second row down
Dim lr as Long
lr = sh3.Range("A" & sh3.Rows.Count).End(xlUp).Row

sh3.Range("A2:A" & lr).AutoFilter 1, ActiveCell.Value