Solved: Delete Only Visible Rows in Table

[Djblois]

I want to only delete rows that are visible in an Excel Data Table. Here is the code that I tried:


wsWorking.ListObjects(1).Range.AutoFilter Field:=lngColumn, Criteria1:=aKeep, _
Operator:=xlFilterValues
Selection.ListObject(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

it keeps giving me an error. Please help

[Kenneth Hobs]

Test on a backup of your data.

e.g.
Sub DeleteHidden()
Dim rngHidden As Range

On Error Resume Next ' In case there's no hidden cells
With Cells
Set rngHidden = .SpecialCells(xlCellTypeVisible)
.EntireRow.Hidden = False 'Unhide all cells
rngHidden.EntireRow.Hidden = True 'Hide previously visible cells
.SpecialCells(xlCellTypeVisible).EntireRow.Delete 'Delete previously hidden cells
rngHidden.EntireRow.Hidden = False ' Unhide previously visible cells
End With
End Sub

Sub KeepHiddenDeleteShown(filteredRange As Range)
Dim t1 As Double, bRange As Range
Set bRange = ActiveSheet.UsedRange
t1 = Timer
Range("A1").AutoFilter Field:=1, Criteria1:="Delete"
Rows(1).EntireRow.Hidden = True
Cells.SpecialCells(xlCellTypeVisible).EntireRow.Delete 'Delete visible rows
Rows(1).EntireRow.Hidden = False
Range("A1").AutoFilter

'MsgBox "Deleted about " & bRange.Rows.Count / 2 & " rows and " & bRange.Count & " cells." & _
vbCrLf & "It took " & CStr(Timer - t1) & " seconds."
End Sub

Sub AdvFilter()
SpeedOn
Dim t1 As Double, bRange As Range
Set bRange = Range("A1:C1000")
Worksheets("CritSheet").Range("IV1").Value = Range("A1")
Worksheets("CritSheet").Range("IV2").Value = "<>Delete"
t1 = Timer
bRange.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Worksheets("CritSheet").Range("IV1:IV2"), Unique:=False
KeepHiddenDeleteShown bRange
MsgBox "Deleted about " & bRange.Rows.Count / 2 & " rows and " & bRange.Count & " cells." & _
vbCrLf & "It took " & CStr(Timer - t1) & " seconds."
Range("A1").Select
SpeedOff
End Sub

[Djblois]

Ken,

That code will not work in Tables. That is the first code that I tried. That will only work in a normal range.

[Kenneth Hobs]

Please attach a short example file.

[Aflatoon]

Please try the following:

With wsWorking.ListObjects(1).Range
.AutoFilter Field:=lngColumn, Criteria1:=aKeep, Operator:=xlFilterValues
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

[Djblois]

aflatoon,

Just tried it - it says, "Delete Method of Range Class Failed."


here is a sample workbook

[Kenneth Hobs]

Since you seem to have a solution at ExcelForum (http://www.excelforum.com/excel-programming/787860-delete-only-visible-rows-in-table.html), please mark this solved.