Delete Hidden/Invisible Rows after Autofilter Excel VBA

[VBA learner ITG]

Good morning peers,

I guess this is pretty straight forward, but for some reason it just does not seem to work for me
I have the below code which auto-filters the data in row A39 based on the criteria that I have specified in Cell A1.

What I am now looking to do is delete all the Unfiltered (Hidden) rows that do not fit the criteria.
Can anyone help as the various amendments i have tried keeps failing on my mac excel!!

[FONT=Menlo][COLOR=#011993]Sub[/COLOR] Copy_Template()[/FONT]
[FONT=Menlo]
[/FONT]
[COLOR=#008F00][FONT=Menlo]' Copy_Template Macro[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]Application.ScreenUpdating = [COLOR=#011993]False[/COLOR][/FONT]
[COLOR=#011993][FONT=Menlo]On[COLOR=#000000] [/COLOR]Error[COLOR=#000000] [/COLOR]GoTo[COLOR=#000000] M[/COLOR][/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]Dim[COLOR=#000000] i [/COLOR]As[COLOR=#000000] [/COLOR]Long[/FONT][/COLOR]
[FONT=Menlo][COLOR=#011993]Dim[/COLOR] Lastrow [COLOR=#011993]As[/COLOR] [COLOR=#011993]Long[/COLOR][/FONT]
[FONT=Menlo]Lastrow = Sheets("list").Cells(Rows.Count, "A").End(xlUp).Row[/FONT]
[FONT=Menlo]    [COLOR=#011993]For[/COLOR] i = 2 [COLOR=#011993]To[/COLOR] Lastrow[/FONT]
[FONT=Menlo]        Sheets("Template").Copy after:=Sheets(Sheets.Count)[/FONT]
[FONT=Menlo]        ActiveSheet.Name = Sheets("List").Cells(i, 2).Value[/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo][COLOR=#ff0000]Range("A39:AF" & Range("AF" & Rows.Count).End(xlUp).Row).AutoFilter Field:=1, Criteria1:=Range("A1").Value[/COLOR][/FONT]
[FONT=Menlo]
[/FONT]
[COLOR=#011993][FONT=Menlo][COLOR=#000000]    [/COLOR]Next[/FONT][/COLOR]
[FONT=Menlo]Sheets("list").Activate[/FONT]
[FONT=Menlo]Application.ScreenUpdating = [COLOR=#011993]True[/COLOR][/FONT]
[FONT=Menlo]
[/FONT]
[COLOR=#011993][FONT=Menlo]Exit[COLOR=#000000] [/COLOR]Sub[/FONT][/COLOR]
[FONT=Menlo]M:[/FONT]
[FONT=Menlo]MsgBox "That sheet name may already exist or be a improper sheet name"[/FONT]
[FONT=Menlo]Application.ScreenUpdating = [COLOR=#011993]True[/COLOR][/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]
[/FONT]
[FONT=Menlo]End Sub[/FONT]

 

[frank_AL]

VBA learner

I haven't taken the time to try to fully understand what all your code is doing but the code snippet below can be used to cycle through all rows in a given range and delete rows that are hidden.

    Set rng = Range("A1:A1000")
    For Each r In rng.Rows
        If r.EntireRow.Hidden Then
            Rows(r).Delete
        End If
    Next r