Code to delete autofilter results Hi,,, I have tried recording a macro then chan

Question

Code to delete autofilter results

Hi,,,

I have tried recording a macro then changing it to suit but can't get anything to work.

I would like a code to autofilter results B3:L & last row for values in B that are >>1 then delete the rows and move the results up.

The file can have over 500k lines (and possibly a lot more!) so a fast code would be greatly appreciated. If it is faster to delete and move up just the data in cols B:L rather than deleteing the entire row then that is also good.

Thanks for your help!

Explanation / Answer

Hi,

if you want to delete all visible rows
(where values in column B are greater than one)

I assume that data is in active sheet and in row 2 are headers

(data starts in row 3 up to row XXX....)

if so,
MAKE A COPY BEFORE
and then try this code...

[Edit]

Sub a_DeleteVisibleRows_01()
'Oct 20, 2014
'data in active sheet
Const N As Long = 2 '<< headers in row 2 ,change
Const myC As String = "B" '<< search for values in column B
Dim r As Long
With ActiveSheet
.AutoFilterMode = False
r = .Cells(Rows.Count, myC).End((xlUp)).Row
.Range(myC & N & ":" & myC & r).AutoFilter Field:=1, Criteria1:=">1"".
.Range(myC & N + 1 & ":" & myC & r).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilterMode = False
End With
End Sub!

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects