Vba to search for a value in column A, if found enter specific text then delete the row

[StevenChin]

Hi,

Hope you can help.

I receive a similar report from different person. In the last row of column A, some reports have Grand Summaries and some reports don't. I have 2 macros that I use depending if there is a grand summaries which is a bit annoying because i always have to check first.

I have been searching for a VBA code but could find a suitable one. I really hope someones can help to create a VBA that can to go to last row and if Grand Summaries exist, I need to run below commends which is select the entire row of the grand summaries and down to the last row of the sheet and delete everything. This is what i run daily.
Range("A1").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp

If the last row doesn't have Grand Summaries on the report then move 1 row down and select the entire row and down to the last row of the sheet and delete everything which is what I am using below.
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearFormats
Selection.Delete Shift:=xlUp

Thanks in advance.

 

[DanteAmor]

Welcome to the MrExcel forum. Please accept my warmest greetings.

With this macro, consider the last row with data starting from A1 and down (just like you have in your examples).

Sub macro1()
  Dim f As Range
 
  Set f = Range("A:A").Find("Grand Summaries", , xlValues, xlPart, , , False)
  If Not f Is Nothing Then
    Range(f.Offset(1), Range("A" & Rows.Count)).Delete Shift:=xlUp
  Else
    Range(Range("A1").End(xlDown).Offset(1, 0), Range("A" & Rows.Count)).Select
  End If
End Sub

With this other macro, consider the last row, but starting from the last row of the sheet and going up.

Sub macro2()
  Dim f As Range
 
  Set f = Range("A:A").Find("Grand Summaries", , xlValues, xlPart, , , False)
  If Not f Is Nothing Then
    Range(f.Offset(1), Range("A" & Rows.Count)).Delete Shift:=xlUp
  Else
    Range(Range("A" & Rows.Count).End(3)(2), Range("A" & Rows.Count)).Select ' Shift:=xlUp
  End If
End Sub

I show it to you, because with the first method, you would only go as far as finding the first empty cell, but there may be several intermediate empty cells and then more data.
Example with first method:

With the second method you will find the last cell with data regardless of whether there are empty intermediate cells above.
Exampel with second method:

Note: There are other ways to find the last row with data on the sheet, but the second method is the most common. You can find them on the web.

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[StevenChin]

Thank you. Will try it out.