Delete all rows in filtered range Except first filtered row in excel VBA

0 votes

I want to delete all rows in the filtered range except the first visible row after the header.

For example,

This is a sample table:

a

b

I want to delete all the filtered rows of apple Except row number 3 which is the first visible filtered row.

I have tried below code :

Sub Filter()
    Dim cl, rng As Range

    Range("A1").AutoFilter Field:=1, Criteria1:="Apple"
    Set rng = Range("A2:A7")
    For Each cl In rng.SpecialCells(xlCellTypeVisible)
        cl.EntireRow.Delete     
    Next cl
End Sub

The problem with this code is that it deletes all the filtered rows. How to specify not to delete the first visible row

Jan 8 in Others by Kithuzzz
• 28,900 points
78 views

1 answer to this question.

0 votes

Use a flag to omit first row

Sub Filter()
    Dim cl as Range, rng As Range ' type all variables, otherwise they'll be Variants
    Dim FirstRow as Boolean

    FirstRow  = True
    Range("A1").AutoFilter Field:=1, Criteria1:="Apple"
    Set rng = Range("A2:A7")
    For Each cl In rng.SpecialCells(xlCellTypeVisible)
        If Not FirstRow Then
            cl.EntireRow.Delete
        End If
        FirstRow = False
    Next cl
End Sub
answered Jan 8 by narikkadan
• 53,520 points

Related Questions In Others

0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 53,520 points
295 views
0 votes
1 answer

Deleting Empty rows in Excel using VBA

On Error Resume Next worksheet.Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete On Error GoTo 0 When ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 53,520 points
175 views
0 votes
1 answer

1-step shortcut to delete entire row in excel 2016

Highlight the whole row by clicking on ...READ MORE

answered Oct 22, 2022 in Others by narikkadan
• 53,520 points
77 views
0 votes
1 answer

Excel VBA remove blank rows from specific range

I have tried to avoid .select  Option Explicit Sub CombineData() ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 53,520 points
186 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
533 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,415 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,960 points
117 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
300 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 53,520 points
1,188 views
0 votes
1 answer

Unhide rows in Excel with VBA

Paste this code into a module of ...READ MORE

answered Oct 1, 2022 in Others by narikkadan
• 53,520 points
168 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP