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, 2023 in Others by Kithuzzz
• 38,010 points
1,357 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, 2023 by narikkadan
• 63,420 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
• 63,420 points
1,336 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
• 63,420 points
950 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
• 63,420 points
271 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
• 63,420 points
738 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
833 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
3,132 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
• 23,260 points
430 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,670 points
692 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
• 63,420 points
2,973 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
• 63,420 points
546 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