Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hiding unused categories in charts | Excel Discussion (Misc queries) | |||
Hiding unused rows | Excel Programming | |||
Hiding unused columns | Excel Programming | |||
Having a floating row or hiding unused rows? | Excel Programming | |||
After hiding unused columns to the right, border on right disappea | Excel Discussion (Misc queries) |