Hi,
I have a table (Table1) with a lot of records. I want to filter, say all 1,2,3,4,5,6,7,8,9,10 in column A (This can be text as well).
After that I want to delete all the rows that are hidden. Is there a better way to do that then I do with this?
Code
Sub Delete_Hidden_Rows_in_Table1()
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10"), Operator:=xlFilterValues
Application.Goto Reference:="Table1"
Selection.Copy
Range("Table1").Select
Selection.End(xlToRight).Select
ActiveCell().Offset(0, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.ListObjects("Table1").AutoFilter.ShowAllData
ActiveSheet.ListObjects("Table1").DataBodyRange.delete
Range("Table1").Select
Selection.End(xlToRight).Select
ActiveCell().Offset(0, 2).Select
ActiveCell.CurrentRegion.Select
Selection.Copy
Application.Goto Reference:="Table1"
ActiveSheet.Paste
Range("Table1").Select
Selection.End(xlToRight).Select
ActiveCell().Offset(0, 2).Select
ActiveCell.CurrentRegion.Select
Selection.ClearContents
Range("Table1").Select
End Sub
Display More
What I do is copy the filtred records to the right of Table1.
Then delete all the records in Table1. After that paste the records back in Table1
Any better way are most welcome...
/Tony