Clearing rows with a zero or "" quantity

[slavoy]

I have a macro that adds the current date to the last non-zero cell in column A of a series of sheets titled "1" through "n". In column B of sheets "1" though "n", in the cell just to the right of where the date is entered, there is a vlookup function that looks for a sheet whose name is a date that matches the date entered in column A (in dd-mmm-yy format) and returns (in column B) a quantity from that sheet.

On some days, the quantity may be zero in column B and the next day will come and the macro will enter the new date in column A just below the previous date and all that will be in the row with the previous date is the date itself. If there are whole bunch of days in a row that this happens, this will take up too many valuable rows in my worksheet and my file size will get way too big.

Can anyone think of some code I could add that would clear the contents (not delete) of a row in sheets "1" though "n" on the condition that a new date is entered in column A and the previous day has a zero quantity in column B?

For example:
.............A.................B
1.........5-Jun-08.........""
2.........6-Jun-08.........35

If this were to happen, I want it to clear the contents of row 1 before the 6-Jun-08 is automatically entered into the worksheet so that 6-Jun-08 is entered into row 1 instead of row 2.

Here is my macro:

Private Sub Workbook_Open()

Dim xlWs As Worksheet
Dim fillRow As Long
Dim rng As Range
Dim i As Long

'First number in the following for loop is always 4, and the second number returns the total
'number of items in column B, which is found in the spreadsheet by using =max(B:B)

For i = 4 To Sheets("Initial Input").Range("O10").Value

'-- this tells which sheet to name what...
Set xlWs = Sheets(i)

'-- find the last non-empty cell in column A
fillRow = xlWs.Range("A65536").End(xlUp).Row

'-- Update sheet only if today's date has not already been entered into it
If xlWs.Range("A" & fillRow).Value <> Date Then

'-- enter today's date
xlWs.Range("A" & fillRow + 1).Value = Date

'-- fill down formula in column B
xlWs.Range("B" & fillRow).AutoFill Destination:=xlWs.Range("B" & fillRow & ":B" & fillRow + 1)

'-- fill down formula in column D
xlWs.Range("D" & fillRow).AutoFill Destination:=xlWs.Range("D" & fillRow & ":D" & fillRow + 1)

End If
Next i

Set xlWs = Nothing

Call CopyandDateSheet

End Sub

 

[Peter_SSs]

Try adding this code just before your existing "End If" line (Hope I have understood what you want)

If xlWs.Range("B" & fillRow).Value = "" Then
     xlWs.Rows(fillRow).ClearContents
End If

 

[slavoy]

Peter,

the code you sent me clears the row, but once it clears it the current date is added BELOW the cleared row so I am left with a blank row and then the current date and a quantity. Can I enter your code and clear the row before the macro enters the current date somehow so that the current date goes in the cleared row?

For example,

........A...............B
1...6-Jun-08.......0
2 ..7-Jun-08........35

Your code does the following to the above setup:

........A...............B
1...........................
2 ..7-Jun-08........35


Where as I want it to do this:

........A...............B
1...7-Jun-08........0


What can I do to fix this?

Thanks for your help.

 

[Peter_SSs]

Well, I don't really know exactly what is on your sheet and how it works but a couple of ideas.

1. Leave my code where I said to put it but change the middle line to:

xlWs.Rows(fillRow).Delete

However, in your original post you said "... clear the contents (not delete) of a row ..." so perhaps you still don't want to delete the whole row?

2. Just delete the offending row in a few columns and shift everything up, by changing the middle line of my code to:

Range("A" & fillRow).Resize(, 2).Delete Shift:=xlUp

Change the "2" in the Resize function if you need to clear out more than two columns (A:B) in that row.

If neither of those is what you want, post back and try to give some more details of how your sheet looks/works.