I would like VBA code to clear data on sheet YTD Total from row 4 up to before where total appears in Col A
Your assistance in this regard is most appreciated
Printable View
I would like VBA code to clear data on sheet YTD Total from row 4 up to before where total appears in Col A
Your assistance in this regard is most appreciated
Hi Howardc
Could you not record a macro for something like that.
Kevin
Hi Kevin
I could record this, but the data range changes
Regards
Howard
Howardc,
I'm a little confused as to what you want. Can you provide a workbook with an example?
If I do not respond today, perhaps another member will pick up. If not I'll have something tomorrow.
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
Howard,
Using your attachment, I have put together the following:
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,Code: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
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
Hi Tony
Thanks for the help, much appreciated
I need the folowing change
I need the data cleared on all the sheets, except the last two sheets-all criteria the same as before
I have tried to do this myself, but it comes up with a compile error when activating the macro-see changes to code & asttachment
RangeToClear = FirstColumnToClear + Format(FirstRowToClear) RangeToClear = RangeToClear + ":"
HTML Code:Sub HowardsClearRoutine()
Const FirstRowToClear = 4
Const FirstColumnToClear = "A"
Const LastColumnToClear = "D"
Const TotalColumn = "A"
Dim i As Long
For i = 1 To Worksheets.Count - 2
Dim LastRowToClear As Integer
Dim RangeToClear As String
Dim LocationOfTotal As Range
Dim Sht As Worksheet
For Each Sht In MyBook.Worksheets
' 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
Next i
End Sub
I have tried to amend the code to ignore the last two sheets when clearing data
Try this
Code: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
Dim lngLOop As Long
' Find the row where the word "Total" is in column A, and clear the contents of rows
' FirstRowToClear through LastRowToClear for columns FirstColumnToClear thru LastColumnToClear
For lngLOop = 1 To ThisWorkbook.Sheets.Count - 2
Set Sht = ThisWorkbook.Sheets(lngLOop)
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
RangeToClear = ""
Next Sht
End Sub
Hi Howardc
The secret here is not to try and monkey with something which is working (I hope - it was OK when I tried it out ;-), but to see what the minimum change is to use it in different ways. Your most recent request is to be able to do this for different sheets. (You say "all except the last two sheets") but you know as well as I that that requirement will change. (The Devil's Computer Dictionary defines "Requirement" as a necessary feature of the software which becomes apparent only after the software has been tested and delivered)
So you have two problems:
1) The original problem, and
2) Deciding which sheets you want to clean up in this way.
The only difference, really, is that you don't know (necessarily) at any moment which sheet you're dealing with. However, this doesn't matter - as long as you can demonstrate that it IS a sheet that you should be clearing, the original procedure will work fine. (Incidentally, if you compare the original that I sent with the version that you returned to me, you'll see that you have squashed to statements onto the same line. I'd leave it the way it was sent - one statement-one line)
So the only change that is needed is to make the sht object a parameter to the procedure, rather than a local variable. That takes care of requirement 1 very easily.
For requirement 2 we are going to add a function that says, in effect, "Do I want to work with this worksheet?" This is the function "TestSheetFor Operation in the second code snippet.
Lastly, we need something to control the whole thing. This is a sub that will iterate over all the sheets in the workbook. It sends each worksheet over to TestSheetForOperation, and if it gets an OK from that function, it calls the ClearSheetTotals function. This piece of code is Sub Driver, in the 3rd code snippet.
Some comments overall.
1) The first part of analyzing requirements is to make certain that you have them all (see Dictionary Definition above)., and to identify any repeated process. In this case the repeated process is the clearing of the totals. Code that first, and figure out what bit of that process will change each time. The changeable bit is going to become a parameter.
2) If there is any question about whether the parameterized code will be done every time, ie if there is a test for whether or not to do it, then put the test into a function. Making the decision (do I call the code) is very different from actually calling the code. If you put decisions like that into a function, then you don't need to change either the driver routine, or the working routine. The only place that would change would be the decision making routine.
3) Your main driving routine can now act as if those tests and operations were simply part of the language. If you look at Driver, it's as if the computer knows how to do everything you want. You know (and I know, and everyone on this list knows ;-) that you had to code those capabilities, but anyone reading the top level of code can understand at a glance what you're trying to do. Whether we like it or not, our code spends 99% of its life in maintenance mode - and the kindest thing you can do for a maintenance programmer is to make your code as easy to read and understand as possible.
Lastly, there is an excellent book that I think might help you. It's called Algorithms + Data Structures = Programs, by Niklaus Wirth, published by Prentice Hall. It's a classic and well worth adding to your library.
HTH,
Tony
Code:Function ClearSheetTotals( sht as worksheet) as boolean
' By defining this procedure as a function we have the possibility of reporting any
' problems back to the calling procedure.
' This function does the "ClearSheet' operation previously defined, for a sheet
' which is passed in as a parameter.
Const FirstRowToClear = 4
Const FirstColumnToClear = "A"
Const LastColumnToClear = "D"
Const TotalColumn = "A"
Dim LastRowToClear As Integer
Dim RangeToClear As String
Dim LocationOfTotal As Range
dim NoErrorsFound as boolean
' Find the row where the word "Total" is in TotalColumn, and clear the contents of rows
' FirstRowToClear through LastRowToClear for columns FirstColumnToClear thru LastColumnToClear
NoErrorsFound=true
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
' Now return the value true or false, depending on whether or not we found any errors
ClearSheetTotals=NoErrorsFound
End Function
Code:Function TestSheetForOperation (sht as worksheet) as boolean
' This function determines if the worksheet passed in needs to have its totals cleared. It's a very
' laborious way of doing it - but allows you to have any pretty arbitrary test. It's also a very inefficient
' algorithm, because the overhe3ad of counting the sheets is done every time. However, a
' little algorithmic inefficiency is worth the clarity of purpose!
dim Book as workbook
dim WrkSheet as worksheet
dim Name as string
dim NumSheets as integer
dim i as integer
' To be on the safe side, we return a value of false by default. We can change this later.
TestSheetForOperation=false
' This is the bit that we do every time, which you would probably do outside once you have your
' test fully defined. For now we don't say "Yes" if the sheet is one of the last 2 sheets, based on index
' within workbook.sheets.count
set Book=ActiveWorkBook
NumSheets=Book.Worksheets.Count
' Now find worksheet in the collection of worksheets
Name=sht.name ' Get the name of the sheet we passed in as a parameter
set WrkSheet=Book.Worksheets(Name) ' Find the sheet in the Workbook
i=WrkSheet.index
if i=0 then
Exit Function ' The parameter sheet does not exist in this workbook!
else
if i>=NumSheets-2 then
Exit Function ' The sheet is in the don't touch area!
else
TestSheetForOperation=true
endif
endif
End Function
Code:
Sub Driver
dim Book as workbook
dim w as integer
' Open the Workbook
set Book=ActiveWorkBook
' and look at every worksheet in it
' to see if we want to clear the totals
for w=1 to Book.Worksheets.Count
if TestWorksheetForOperation (Book.Worksheets(w)) then
ClearSheetTotals(Book.Worksheets(w))
endif
next w
End Sub
Hi Guys
Thanks for the help and detailed explanations
Regards
Howard