Clear Specific Rows Of Data On Multiple WorkSheets

[Howardc]

Hi Charles

Attached please find spreadsheet with Data

I need a macro to do clear all the data from row 4 inColumns A:B & D up to the row before where total appears

Regards

Howard

[tfurnivall]

Howard,

Using your attachment, I have put together the following:

Sub HowardsClearRoutine()

Const FirstRowToClear = 4
Const FirstColumnToClear = "A"
Const LastColumnToClear = "D"
Const TotalColumn = "A"

Dim LastRowToClear As Integer
Dim RangeToClear As String
Dim LocationOfTotal As Range

'   Find the row where the word "Total" is in column A, and clearthe contents of rows
'   FirstRowToClear through LastRowToClear for columns FirstColumnToClear thru LastColumnToClear
Set Sht = ActiveSheet       '   You may want to be a little more specific about this!

Set LocationOfTotal = Sht.Columns(TotalColumn).Find("Total")
'   Last row to clear is the row BEFORE the Total
LastRowToClear = LocationOfTotal.Row - 1
'   Now construct the range which we will be clearing
RangeToClear = FirstColumnToClear + Format(FirstRowToClear)
RangeToClear = RangeToClear + ":"
RangeToClear = RangeToClear + LastColumnToClear + Format(LastRowToClear)
'   Do we want to clear the contents, or delete the cells? This example simply clears the contents
Sht.Range(RangeToClear).ClearContents


End Sub

A couple of points. It's ALWAYS better to use constants rather than magic numbers. If you have to go through the code when an extra header row is added,
you have to remember the importance of every single "4" in your code. Imagine if you had NumHeaderRows, NumSeasons, NumDivisionsIn AFC etc. Not only is the code easier to read, but it makes you think a little bit more about it, as you are typing it in!

The task is basically, construct a range which represents the cells you want to clear. That's really all this example does. It does it very slowly (so you can see how the range grows!). If you simply want to clear the cells (which is what I have done, to preserve the test data ;-) it will work as is. If you want to delete the cells (you did indicate that the location of the Total may change), then you should substitute Sht.Range(RangeToClear).Delete (xlShiftUp). You use xlShiftUp to make certain that the
cells that used to contain data disappear, without destroying that nifty little look-up area you have.

HTH

Tony