Unable to amend Macro range

[amandeep08]

I have a macro that will delete rows if Word GROSS found in column G but it is picking data from G1 till G End. I want to amend the macro where the data will be deleted from G4 onwards.



Sub DeleteRowWithContents()
'========================================================================
' DELETES ALL ROWS FROM A2 DOWNWARDS WITH THE WORDs "Gross" IN COLUMN G
'========================================================================
Last = Cells(Rows.Count, "g").End(xlUp).Row
For i = Last To 3 Step -1
If (Cells(i, "g").Value) = "Gross" Then
'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
Cells(i, "A").EntireRow.Delete
End If
Next i
End Sub

 

[Michael M]

Try

Sub DeleteRowWithContents()
Dim lr As Long, i As Long
lr = Cells(Rows.Count, "g").End(xlUp).Row
For i = lr To 4 Step -1
If (Cells(i, "g").Value) = "Gross" Then
Rows(i).ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
'Rows(i).Delete
End If
Next i
End Sub

 

[Saurabhj]

Hi Amandeep,

Please check below code:

Sub DeleteRowWithContents()
'========================================================================
' DELETES ALL ROWS FROM A4 DOWNWARDS WITH THE WORDs "Gross" IN COLUMN G
'========================================================================
last = Cells(Rows.Count, "g").End(xlUp).Row
i = 4
Do While i <= last
    If (Cells(i, "G").Value) = "Gross" Then
        'Cells(i, "A").EntireRow.ClearContents ' USE THIS TO CLEAR CONTENTS BUT NOT DELETE ROW
        Cells(i, "A").EntireRow.Delete
        i = i - 1
        last = last - 1
    End If
    i = i + 1
Loop
End Sub

 

[Michael M]

Shorter

Sub DeleteRowWithContents()
Dim i As Long
For i = Cells(Rows.Count, "g").End(xlUp).Row To 4 Step -1
If Cells(i, "g").Value = "Gross" Then Rows(i).ClearContents ' OR Rows(i).Delete
Next i
End Sub