0

I want to use VBA to 1) Find the word “Report:” in my excel sheet (I know the word “Report:” will only appear once in the sheet); 2) Erase all the content (including the word “Report”) below this cell

enter image description here

So, ideally, the result should look like this:

enter image description here

The amount of data will change, so the word “Report:” is not going to be in the row 109 every time.

This is the code I am using now,

Sub Trial()

Set myRange = ActiveDocument.Content
myRange.Find.Execute FindText:="Report:", _
    Forward:=True
If myRange.Find.Found = True Then
    myRange.SetRange (myRange.End + 1), ActiveDocument.Content.End
    myRange.Delete
End If
End Sub

But, it gives me a

run-time error ‘424’

braX
  • 11,506
  • 5
  • 20
  • 33
Beans
  • 139
  • 5
  • 19
  • 1
    That code is for Microsoft Word. Use the macro recorder here. – BigBen Feb 07 '20 at 17:01
  • Weirdly enough, [a similar thing](https://stackoverflow.com/questions/60100501/vba-excel-border-around-the-specified-picture#comment106295906_60100501) happened yesterday too. – BigBen Feb 07 '20 at 17:03
  • @BigBen Hmm...but I thought the macro recorder can only erase the content in a pre-defined range, since my range (row number) will change, isn't the macro recorder useless in this case? – Beans Feb 07 '20 at 17:29
  • 1
    Well you have to adjust the code the macro recorder produces... but no, it's *very* useful in this case. Much more useful than copying code found on the web, as it seems you may have done. – BigBen Feb 07 '20 at 17:30

1 Answers1

1

Give this a try:

Option Explicit
Sub ReportKiller()
    Dim Report As String, r As Range, rKill As Range
    Report = "Report"
    Set r = Cells.Find(Report, after:=Cells(1, 1))
    If Not r Is Nothing Then
        Set rKill = Range(r, r.End(xlDown))
        rKill.EntireRow.Delete
    End If
End Sub

It will delete all sheet content from the Report cell downward.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Plz disregard my previous comment. This worked! It's just in my real sheet, I had some blank rows between rows contain data (after the word "Report") and this code will stop when it hits a blank row. – Beans Feb 07 '20 at 17:34
  • 1
    @EmmaG then you can [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and delete down to that. – BigBen Feb 07 '20 at 17:34
  • But I have an idea how to work around it. Thank you! – Beans Feb 07 '20 at 17:34