Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows Filtered within Auto Filter
Hi...
I have 3000 records. I want to delete 300 rows that share an auto filtered value. The problem of course is that when I highlight the block to delete, it deletes the rows that have't been filtered. Is there a way round this? Thanks Gordon... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows Filtered within Auto Filter
Hi Gordon,
Try: '============= Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Set WB = Workbooks("YourBook.xls") '<<=== CHANGE Set SH = WB.Sheets("Sheet1") '<<=== CHANGE On Error Resume Next Set Rng = SH.AutoFilter.Range Set Rng = Rng.Offset(1).Resize(Rng.Rows.Count - 1) Set Rng = Rng.SpecialCells(xlVisible) On Error GoTo 0 If Not Rng Is Nothing Then Rng.EntireRow.Delete End If End Sub '<<============= You could also perform the operation manually: Select cells to be deleted F5 | Special | Visibile cells Right-click | Delete | Entire row --- Regards, Norman "Gordon" wrote in message ... Hi... I have 3000 records. I want to delete 300 rows that share an auto filtered value. The problem of course is that when I highlight the block to delete, it deletes the rows that have't been filtered. Is there a way round this? Thanks Gordon... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you apply the auto filter to data that are filtered? | Excel Worksheet Functions | |||
How do I get Auto filter to report number of records filtered? | Setting up and Configuration of Excel | |||
using auto filter, how to tell what columns are filtered? | Excel Discussion (Misc queries) | |||
Cut filtered rows, paste into next empty row of new sheet, and delete cut rows | Excel Worksheet Functions | |||
Auto Filter Delete Rows by Criteria Doesn't Work Range To Complicated | Excel Programming |