Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Clear Row Contents (Excel 97 SR2)

    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
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear Row Contents (Excel 97 SR2)

    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?
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear Row Contents (Excel 97 SR2)

    Legare,
    I want to do it in VBA code and the word will only appear once in column B.
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Clear Row Contents (Excel 97 SR2)

    <pre> 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
    </pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear Row Contents (Excel 97 SR2)

    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.
    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear Row Contents (Excel 97 SR2)

    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
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Clear Row Contents (Excel 97 SR2)

    Replace
    <pre> With ActiveSheet</pre>

    Use instead
    <pre> With ws</pre>

    or since ws is so short, leave out the with block and put ws in front of all the dots. HTH -- Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear Row Contents (Excel 97 SR2)

    Thanks Guys! It works like a charm!!
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •