Hiding Unused Rows
 

I have a procedure that hides rows in a budget spreadsheet that have no
value when there is a sum in the row. I would also like it to NOT hide
the row if the row in column A - the category, has a text value in it.
Some of these categories are using column A and B formatted continuous.

Here is the procedure as it stands. Some of the code is not necessary.
Will clean up later.

Regards,

Dave Johnston

Sub HideRows()
'On Error Resume Next

Dim bottomright As Range
Dim topleft As Range
Dim CategoryColumn As Range

Set topleft = ActiveSheet.Range("A10")
'topleft.Select
'MsgBox ActiveWindow.RangeSelection.Address
Set rightedge = topleft.Offset(0, 5)
'Set rightedge = topleft.End(xlToRight)
'rightedge.Select
'MsgBox ActiveWindow.RangeSelection.Address
'Set bottomright = rightedge.End(xlDown)
Set bottomright = rightedge.Offset(240, 0)
'bottomright.Select
'MsgBox ActiveWindow.RangeSelection.Address
'bottomright = bottomright.Address(, , xlA1)
With Range(topleft.Address(, , xlA1) & ":" & bottomright.Address(, ,
xlA1))
'With Range("A10:F222")
..EntireRow.Hidden = False


For i = 1 To .Rows.Count
'Trying to set a range for column A and the row number that is being
checked
'so that if column A has anything in it the row is NOT hidden
'Set CategoryColumn = topleft.Address(, , xlA1)
'And IsEmpty(.Rows(i))
If WorksheetFunction.IsText(.Rows(i)) = False Then '.Rows(i)
shows value 1 being the first row starting at the beginning of the
range
If WorksheetFunction.Sum(.Rows(i)) = 0 Then 'I need it to show
row 10
.Rows(i).EntireRow.Hidden = True
End If
'End If


Next i
End With
End Sub

Hiding Unused Rows
 

I found the solution and here it is.




Sub HideRows()
On Error Resume Next


ActiveSheet.Unprotect

With Range("E10:F247")
..EntireRow.Hidden = False
For i = 1 To .Rows.Count

'MsgBox WorksheetFunction.CountBlank(.Rows(i))

If WorksheetFunction.CountBlank(.Rows(i)) < 2 Then
If WorksheetFunction.Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
End If


Next i
End With

ActiveSheet.protect

End Sub


wrote:
I have a procedure that hides rows in a budget spreadsheet that have no
value when there is a sum in the row. I would also like it to NOT hide
the row if the row in column A - the category, has a text value in it.
Some of these categories are using column A and B formatted continuous.

Here is the procedure as it stands. Some of the code is not necessary.
Will clean up later.

Regards,

Dave Johnston

Sub HideRows()
'On Error Resume Next

Dim bottomright As Range
Dim topleft As Range
Dim CategoryColumn As Range

Set topleft = ActiveSheet.Range("A10")
'topleft.Select
'MsgBox ActiveWindow.RangeSelection.Address
Set rightedge = topleft.Offset(0, 5)
'Set rightedge = topleft.End(xlToRight)
'rightedge.Select
'MsgBox ActiveWindow.RangeSelection.Address
'Set bottomright = rightedge.End(xlDown)
Set bottomright = rightedge.Offset(240, 0)
'bottomright.Select
'MsgBox ActiveWindow.RangeSelection.Address
'bottomright = bottomright.Address(, , xlA1)
With Range(topleft.Address(, , xlA1) & ":" & bottomright.Address(, ,
xlA1))
'With Range("A10:F222")
.EntireRow.Hidden = False


For i = 1 To .Rows.Count
'Trying to set a range for column A and the row number that is being
checked
'so that if column A has anything in it the row is NOT hidden
'Set CategoryColumn = topleft.Address(, , xlA1)
'And IsEmpty(.Rows(i))
If WorksheetFunction.IsText(.Rows(i)) = False Then '.Rows(i)
shows value 1 being the first row starting at the beginning of the
range
If WorksheetFunction.Sum(.Rows(i)) = 0 Then 'I need it to show
row 10
.Rows(i).EntireRow.Hidden = True
End If
'End If


Next i
End With
End Sub