VBA to delete filtered Rows

[GorD]

Hope someone can help me with some code.

I have recorded a macro which deletes the visible rows after an auto filter is run - ( auto filter is for custom and blanks).

I thought this was working, but when i look at the code, it looks as if the rows to delete have been hard coded in, and not adapting to just delete the filtered ones.

Many thanks in advance

 

[VoG]

Here's an example that you may be able to adapt

Sub Del_6()
With Columns("W")
    .AutoFilter field:=1, Criteria1:=6
    .Resize(Rows.Count - 1).Offset(1).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False
End Sub

 

[Andrew Poulsom]

You can try eg:

With ActiveSheet.AutoFilter.Range
    .Offset(1).Resize(.Rows.Count -1).EntireRow.Delete
End With

 

[GorD]

Many thanks - i'll give those a go - sure they'll work a treat

 

[GorD]

Could you explain the line

AutoFilter field:=1, Criteria1:=6

which looks to be the main difference between the two codes.

I'm guessing that it is filtering on the first column of the range ( does the line above make it a single column range anyway?) and looking for the value of 6.

I would have to modify this to filter on blanks, i read - Criteria1:="=" somewhere.

Does that look right, am i on the right lines?

 

[Andrew Poulsom]

The macro recorder will give you the AutoFilter code.

 

[rajinder]

[h=2]i hv 10 lac rows line item xls file i want to delete 2 lac line item via auto filter mode below said vba program only deleted 9000 line item . how can i give range below said VBA code so that its deleted data in selected lac rows line item , pls help its urgent.[/h]
Sub DeleteFilteredData()

'Declare the variables
Dim rngFilt As Range
Dim CellCount As Long
Dim Msg As String

'If the data has not been filtered with the AutoFilter, exit the sub
With ActiveSheet
If .AutoFilterMode = False Or .FilterMode = False Then
MsgBox "Please filter the data with the AutoFilter, and try again!"
Exit Sub
End If
End With

With ActiveSheet.AutoFilter.Range

'For Excel 2007 and earlier, check for the SpecialCells limitation
If Val(Application.Version) < 14 Then

On Error Resume Next
CellCount = .Columns(1).SpecialCells(xlCellTypeVisible) _
.Areas(1).Cells.Count
On Error GoTo 0

If CellCount = 0 Then
Msg = "The SpecialCells limit of 8,192 areas has been "
Msg = Msg & vbNewLine
Msg = Msg & "exceeded for the filtered value."
Msg = Msg & vbNewLine & vbNewLine
Msg = Msg & "Tip: Sort the data, and try again!"
MsgBox Msg, vbExclamation, "SpecialCells Limitation"
GoTo ExitTheSub
End If

End If

'Set the filtered range
On Error Resume Next
Set rngFilt = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

'Delete the filtered data
If Not rngFilt Is Nothing Then
rngFilt.EntireRow.Delete
Else
MsgBox "No records are available to delete...", vbExclamation
End If

End With

ExitTheSub:

'Clear the filter
ActiveSheet.ShowAllData

End Sub

 

[rajinder]

Dear Sir ,

Its work on 2003 only i need 2007 and earlier , i am new i don't know where changing is required in above said codes . Please help

 

[fshhady]

Sir

I am just using DataBodyRang to delete the filtered data form the table and its working just fine.

With sheet1.ListObjects("table1")
'Filter
.Range.AutoFilter 1,"5"

'Delete filtered table rows
.DataBodyRange.Delete

'remove filer
.Range.Autofilter
End With

plz let me know if its right
till now I did not lose any data
I am using Excel 2010

Thanks