CODEWITHSUNDEEP
vbaexcel
TurboCoder
TurboCoder

Reputation: 1011

VBA - Delete Visible Rows After Filter Applied

I have the two Macro's which filters a worksheet column by date and then delete's the irrelevant columns.

However, it is very buggy and I need some help to correct that please.

This is what the second Macro is supposed to do:

1) Simply delete the visible rows after the filter is applied, except the first row (headers) - Currently, it deletes ALL visible rows including the first row, even though I have the Offset function in my code.

2) Remove all filters - This is working fine now

Sub DeleteVisibleRows()

Dim ws1 As Worksheet
Dim WorkRng As Range

Set ws1 = ActiveWorkbook.Sheets("Consolidated")

On Error Resume Next

Set WorkRng = Application.Selection

Application.ScreenUpdating = False
With ws1

WorkRng.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).EntireRow.Delete

ws1.AutoFilterMode = False

End With

Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 28808

Answers (3)

Tehscript
Tehscript

Reputation: 2556

Deleting rows of filtered data is something I needed time to time while working with tables in excel, but I could never trust a macro when it comes to deleting important stuff. If you still want to use it, this might work for you:

Sub DeleteVisibleRows()
Dim ws As Worksheet
Dim lastrow As Long, i As Long

Set ws = ThisWorkbook.Worksheets("Consolidated")
With ws
    lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
    For i = lastrow To 2 Step -1 'To 2 Assuming first row contains headers
        If .Rows(i).Hidden = False Then
            .Rows(i).Delete
        End If
    Next
.ShowAllData 'remove filtered data
End With
End Sub

Upvotes: 1

Davesexcel
Davesexcel

Reputation: 6984

SpecialCells will work like the previous answer.

    Sub Button1_Click()
    Dim sh As Worksheet, rng As Range, LstRw As Long

    Set sh = Sheets("Sheet1")
    With sh
        LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set rng = .Range("A2:A" & LstRw).SpecialCells(xlCellTypeVisible)
        rng.EntireRow.Delete
        .AutoFilterMode = False
    End With
End Sub

Upvotes: 5

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

If you're using SELECTION there's no need to define the worksheet. Everything you need is relevant to your selection - your selection may not be on the Consolidated worksheet, it will always be on the parent object of your selection though.

The code below assumes you have a filter applied - if it isn't then everything below the heading gets deleted.

Public Sub DeleteVisibleRows()

    Dim WorkRng As Range

    Set WorkRng = Selection

    With WorkRng
        .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    WorkRng.Parent.AutoFilterMode = False

End Sub

Edit: That code's too long, I'll get rid of some of the junk.

Public Sub DeleteVisibleRows()

    With Selection
        .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .Parent.AutoFilterMode = False
    End With

End Sub

Upvotes: 0

Related Questions