D
davjoh123
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
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