Delete selected row, and all rows below *solved*

[thorpyuk]

Hiya,

I have the following code, to find the 1st cell in column CG that contains a value of more than -200. I wish to delete the entire row that this resides on, and all rows below. Please can anyone help? Thanks!

Range("CG2").Select
    Do While Not Selection.Value > -200
        Selection.Offset(1, 0).Select
    Loop

 

[Lewiy]

Try this:

Sub DeleteMe()
Dim c As Long
Dim Limit As Long
Limit = Cells(Rows.Count, 85).End(xlUp).Row
For c = 1 To Limit
    If Cells(c, 85) > -200 Then
        Rows(c & ":" & Limit).Delete shift:=xlUp
        Exit Sub
    End If
Next c
End Sub

 

[thorpyuk]

eek, that seems to wipe everything :f

 

[schielrn]

Maybe modify to this:

Sub DeleteMe() 
Dim c As Long 
Dim Limit As Long 
Limit = Cells(Rows.Count, 85).End(xlUp).Row 
For c = 2 To Limit 
    If Cells(c, 85).Value > -200 Then 
        Rows(c & ":" & Limit).Delete shift:=xlUp 
        Exit Sub 
    End If 
Next c 
End Sub

I modified Lewiy's code to start at row 2 and look at the value in cell CG2 and down

 

[thorpyuk]

thanks schielrn, that works a treat