Clear Row Contents (Excel 97 SR2)

Topic

New Reply

What is the fastest way to clear the contents of all rows after the row that contains the word “Total”? I can search for the word and then use offset to get to the next row. But then I don’t know how to say from this row on, clear all content.

TIA

Reply | Quote

Replies

Are you looking to do this in VBA code or manually? Is the word “Total” in a particular column, or can it be anywhere? Can “Total” appear more than once, and if so which one do you want to clear after?

Reply | Quote

Legare,
I want to do it in VBA code and the word will only appear once in column B.

Reply | Quote

    Dim c As Range
    With ActiveSheet
        Set c = .Cells.Find(What:="Total", After:=.Cells(1), LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=True)
        If Not c Is Nothing Then _
        .Rows(c.Row + 1 & ":" & .UsedRange.Row + .UsedRange.Rows.Count).Delete
    End With
    Set c = Nothing

Reply | Quote

Sammy beat me to it with his code. One additional comment. It you want to clear the contents of those cells without clearing the formatting like your subject says, then replace the .Delete in his code with .ClearContents.

Reply | Quote

Sam,
The code I already have in place starts out with For each ws in Worksheets and then proceeds to do a series of actions on each sheet. I took your code and inserted it minus the With ActiveSheet to see if it would work. I get a Compile Error that says Invalid or unqualified reference and the error message highlights the code =.Cells as bolded here:
Set c = .Cells.Find(What:=”Total”, After:=.Cells(1), LookIn:=xlValues, _ etc.

BTW, I did try the code as is as a stand alone and it worked fine, clearing the contents of all rows after the row containing the word TOTAL. Now I just need to figure out how to get it to work inside my current code.

TIA

Reply | Quote

Replace

    With ActiveSheet

Use instead

    With ws

or since ws is so short, leave out the with block and put ws in front of all the dots. HTH — Sam

Reply | Quote

Thanks Guys! It works like a charm!!

Reply | Quote