Solved

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

Posted on 2016-08-10
3
100 Views
Last Modified: 2016-08-12
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
'

'
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$T$298").AutoFilter Field:=8, Criteria1:="EUR"
    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.EntireRow.Delete
    Rows("1:1").Select
    Selection.AutoFilter
End Sub
Example.xlsm.xlsx
0
Comment
Question by:JCutcliffe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 36

Accepted Solution

by:
Kimputer earned 500 total points
ID: 41750151
Your code already works, except if row 2 has EUR in it.

change the code from Range("A3").Select to Range("A2").Select
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41750164
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

0
 
LVL 20

Expert Comment

by:Roy Cox
ID: 41750608
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
    ws.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
clean_up:
    ws.Range("A1").AutoFilter
    Set ws = Nothing
    On Error GoTo 0
    Exit Sub

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

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question