VBA code to clean up a sheet (Entire Row Delete)

[BallGazer]

Hi Guys

I hope someone with more VBA than I have can help. I am trying to create code to clean up a sheet. What I need is to test the values in Column "T" starting at "T2". The cells in this column contains either a space or a delete flag (set at 1). If the flag is set to "1" I need to delete the entire row. The sheet contains ~ 300 rows. My attempts are falling foul of the relative positioning once I have deleted a row. I would appreciate any help with suitable code to run this cleanup.

Many thanks

BallGazer

 

[xld]

You need to work bottom up

With Activesheet

    LastRow = .Cells(.Rows.Count, "T").End(xlUp).Row
    For i = LastRow To 2 Step -1

        If .Cells(i, "T").Value = 1 Then

            .Rows(i).Delete
        End If
    Next i
End With

 

[Colin Legg]

Hi BallGazer ,

Why not use the autofilter? Filter column T2 so as to show the rows you want to delete, and then delete the visible rows?

If I needed to automate this process using VBA then this would also be the approach I would take.

HTH

 

[VoG]

Try

Sub Cleanup()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("T" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If Range("T" & i).Value = 1 Then Rows(i).Delete
Next i
Application.ScreenUpdating = True
End Sub

 

[pavin]

Try this code:

Sub Test()
    Columns("T:T").AutoFilter
    Range("T1").Select
    Selection.AutoFilter Field:=1, Criteria1:="1"
    Range(Rows("2:2"), Range("T2").End(xlDown)).EntireRow.Delete Shift:=xlUp
    Selection.AutoFilter
End Sub

 

[BallGazer]

Thank you all. Problem solved.

Regards

BallGazer