Macro Fails

[Sandy]

Hello

I have a monthly report that has 31 rows of data for each day. The
following code is used to display only the current day. The first cell in
Column AG for each day contains the date and the other cells for that day in
col AG are blank. The code works except for the the 31st of the months that
have 31 days. The code is failing on
Rows(rng.Row & ":" & rng.Row + 30).Hidden = False and it appears as though
the code is trying to hide all rows of the sheet.

Does anyone see what may be causing this, and how do I fix it?

Private Sub CommandButton1_Click()
Dim FindString As Date
Dim rng As Range

Application.ScreenUpdating = False
FindString = Date
Columns("AG:AG").Select
Selection.EntireColumn.Hidden = False
Rows(3 & ":" & Rows.Count).Hidden = False

With Sheets("MySheet").Range("AG:AG")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then

Rows(3 & ":" & Rows.Count).Hidden = True
Rows(rng.Row & ":" & rng.Row + 30).Hidden = False
Application.Goto rng, True
Else
MsgBox "Date Not Found"
End If
End With
Columns("AG:AG").Select
Selection.EntireColumn.Hidden = True
Application.ScreenUpdating = True
Range("C1").Select
End Sub

 

[TomPl]

The problem is that you are trying to unhide rows that are not in the used
range of the worksheet. That is, the last row you are hoping to unhide has
no data anywhere in the row and Excel doesn't know it exists. If you put a
value in a cell below the 31st item on the 31st day you macro will run just
fine.

Tom

 

[Sandy]

Thank you!!

The problem is that you are trying to unhide rows that are not in the used
range of the worksheet. That is, the last row you are hoping to unhide has
no data anywhere in the row and Excel doesn't know it exists. If you put a
value in a cell below the 31st item on the 31st day you macro will run just
fine.

Tom