To hide and unhide empty rows in between rows with data in them...

Posted by Chris Rock on June 25, 2001 12:10 PM

I have a worksheet that I update daily. It contains entries for each day in the month (one day per row), and then a Totals Row in row 35.

Everything in between the current day and the total needs to be hidden, so each day I manually select the rows between the current day and the Totals Row, and I hide them. The following day, I unhide all the rows, and re-hide the rows between the NEW current day and the Totals Row.

Can someone help me automate this process? I'm guessing the easiest thing would be a macro that unhides the top row of many hidden rows.

Any Ideas?

Posted by Ben O. on June 25, 2001 12:59 PM

I can only think of a way to automate the process if you have the =TODAY() formula somewhere in your worksheet. For some reason TODAY can't be access through VBA with Application.WorksheetFunction like many Excel fuctions can. But if you have =TODAY in a cell (H1 in my example), this macro will automate the hiding/unhiding (it also assumes your dates are in the first column and start on row 2):

Sub Auto_Open()
Rows.Hidden = False
cTime = Range("H1")
For x = 3 To 34
If Cells(x, 1).Value > cTime Then
Cells(x, 1).EntireRow.Hidden = True
End If
Next x
End Sub

Naming the macro Auto_Open makes it run when your worksheet is opened.

-Ben