How to delete rows after an End (xl Down) without deleting header row?

Hi Guys, I have recorded an Excel macro which puts a Filter in Column 8  for the word "EUR" , then an End (xl down) and then deletes the rows, but I do not want to delete the header row so how do I adapt the code to do this? here's the code:

Sub Macro1()
' Macro1 Macro

    ActiveSheet.Range("$A$1:$T$298").AutoFilter Field:=8, Criteria1:="EUR"
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
End Sub
Who is Participating?
KimputerConnect With a Mentor Commented:
Your code already works, except if row 2 has EUR in it.

change the code from Range("A3").Select to Range("A2").Select
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Try something like this......

Sub DeleteFilteredRows()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
With Rows(1)
    .AutoFilter field:=8, Criteria1:="EUR"
    If Range("A1:A" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
        Range("A2:A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End If
End With
ActiveSheet.AutoFilterMode = 0
End Sub

Open in new window

Roy CoxGroup Finance ManagerCommented:
This code will prompt the user for a item delete, e.g. EUR. The relevant rows will then be deleted.
Option Explicit

' Procedure :   Delete_AutoFiltered_Rows
' Author    :   Roy Cox
' Date      :   13/02/2015
' Purpose   :   Filter Excel Table, delete visible range
Sub Delete_ListRows_AutoFilter()

    Dim ws As Worksheet
    Dim rng As Range
    Dim strCriteria As String
On Error GoTo err_exit
    '///ask what the use wants to delete
    strCriteria = Application.InputBox("What do you want to delete")
    '/// the sheet containing the Data table
    Set ws = Sheet1

    '///DataBodyRange to Range
    Set rng = ws.Range("A1").CurrentRegion
    On Error GoTo err_exit
    'Filter the Range
    rng.AutoFilter Field:=8, Criteria1:=strCriteria
    '///Delete the visible range
    Set ws = Nothing
    On Error GoTo 0
    Exit Sub

    MsgBox "No range was found or the user cancelled"
    Resume clean_up
End Sub

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.