Hi All,
I have a report with lots of time data, format [hh]:mm:ss using Excel 2010 at home but use 2003 at work.
I would like to replace 00:00:00 with "" (blank), so used the macro recorder and performed the steps manually which worked fine.
The problem is I have tried to make it generic, and it seems to run the macro but does not replace the values. I was wondering if someone could spot something I'm missing. I can mock up a demo sheet as the original contains confidential data, but was hoping it was a code/logic error. The code is below:
Public Sub removeZeroTimes(rngChosen As String)
' removeZeroTimes - erases values of 00:00:00 from selection
Range(rngChosen).Select
Selection.Replace What:="00:00:00", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Public Sub cleanupReport()
' Tidy up report - remove zeroes etc
' b5:w<lastDataRow> would do in 1 go
Dim rngRepEyeOver As String
Dim rngRepMealOver As String
Dim rngRepWGMTime As String
Dim rngRepWTLLTime As String
Dim rngRepORGTime As String
Dim rngRepOtherTime As String
Dim tempRepLastRow As String
Dim lastRepDataRow As String
' get last row
tempRepLastRow = getLastRow()
' now account for summary row and gap between
lastRepDataRow = tempRepLastRow - 3
' define dynamic ranges for report columns
rngRepEyeOver = "Q5:Q" & lastRepDataRow
rngRepMealOver = "S5:S" & lastRepDataRow
rngRepWGMTime = "T5:T" & lastRepDataRow
rngRepWTLLTime = "U5:U" & lastRepDataRow
rngRepORGTime = "V5:V" & lastRepDataRow
rngRepOtherTime = "W5:W" & lastRepDataRow
' strip zero times from Eye Break Over and Meal Break Over
Call removeZeroTimes(rngRepEyeOver)
Call removeZeroTimes(rngRepMealOver)
' do any other tidy up here - TODO
End Sub
Any help would be greatly appreciated.
Thank you
Pete Austin
Bookmarks