Delete an entire region below specific Cell that contains a specific text-CodePudding

I want VBA to Find a cell with text that contains "USD Receipts:" and then delete the entire row and the current region under this cell till it finds a blank row

I tried to apply the below Macro, but it deletes all rows below that cell not just the region under this cell.

Sub NONHM()
Dim LR As Long, Found As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Found = Columns("A").Find(what:="USD RECEIPTS:", LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then Rows(Found.Row & ":" & LR).Delete
End Sub

CodePudding user response：

You want to use xldown here since you want to stop at the first blank not the last blank and base the row number not on the total count of rows but on where you found your value.

Sub NONHM()
Dim LR As Long, Found As Range
Set Found = Columns("A").Find(what:="USD RECEIPTS:", LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then 
    LR = Range("A" & found.row).End(xldown).Row
    Rows(Found.Row & ":" & LR).Delete
end if
End Sub