VBA that hides rows based on reference cell

**brandnka** · 09-03-2010, 11:11 AM

I'm trying to create a log sheet that will show a set number of rows based on two 'input' cells. I found the following thread that could help me along, but I can't get it working quite right for what I want to do http://www.excelforum.com/excel-gene...hide-rows.html

Attached is my sheet, and the code that I think will get me most of the way to what I want to do is below:

Public Cntr, n, colIndex
Public TakeAction
Sub UnHideAllRows()
    Cells.Select
    Selection.EntireRow.Hidden = False
    Range("A6").Select
End Sub
Sub DynamicHiding()
    ReDim SheetsFound(0)
    For Each aSheet In ActiveWorkbook.Sheets
        If InStr(1, aSheet.Name, "(") = 0 Then
            Select Case TakeAction
                
            Case Is = 70
                HideRowsWhenZero
                'aSheet.PrintOut
            Case Is = 80
                aSheet.Activate
                UnHideAllRows
            End Select
        End If
    Next aSheet
    Sheets(1).Activate
End Sub

Sub HideRows()
BeginRow = 10
EndRow = 350
ChkCol = 8
Application.ScreenUpdating = False
    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = 0 Then
        Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
Application.ScreenUpdating = True
End Sub

Sub UnhideRows()
    Application.ScreenUpdating = False
        Cells.Select
        Selection.EntireRow.Hidden = False
        Range("A1").Select
    Application.ScreenUpdating = True
End Sub

Taking a look at my file will help explain what i would like to do. On the far left are yellow boxes, with 11 rows beneath each. The yellow boxes are the # of products referenced in EG9. I want to have EG9 affect how many of the yellow boxes are visible along with the number of white boxes beneath each being determined by EG8. I'm not very proficient at VBA, so help is greatly appreciated.

**mrice** · 09-04-2010, 08:22 AM

Try this

Sub HideRowsMJR()
Dim N As Integer
Dim Visible As Boolean
For N = Cells(Rows.Count, 1).End(xlUp).Row - 1 To 10 Step -1
    Visible = True
    If N > (Range("EG9") * 12) + 9 Then
        Visible = False
    End If
    If (N - 10) Mod 12 > Range("EG8") - 1 Then
        Visible = False
    End If
    If Visible = False Then Rows(N).RowHeight = 0
Next N
End Sub

**Cheeky Charlie** · 09-04-2010, 11:16 AM

mrice, much better than OP's (with respect, though I'm sure you aren't precious, brandnka)
Couple of thoughts:

Integer only goes up to 32k - if the spreadsheet is large this would fall over - better choice would be long (up to 2,000M)

You can use logical statements to return booleans directly:

    If N > (Range("EG9") * 12) + 9 Then
        Visible = False
    End If

=

visible = N<= (Range("EG9") * 12) + 9

Similarly:

If Visible = False Then Rows(N).RowHeight = 0

=

rows(n).hidden = not visible

You have two if loops to set the same variable,

    If N > (Range("EG9") * 12) + 9 Then
        Visible = False
    End If
    If (N - 10) Mod 12 > Range("EG8") - 1 Then
        Visible = False
    End If

=

    If N > (Range("EG9") * 12) + 9 or (N - 10) Mod 12 > Range("EG8") - 1 Then
        Visible = False
    End If

(obviously could be simplified as outlined above to set the boolean directly without if)

You code hides rows, but won't unhide them if the number of products or rows per product is increased
(this is the only show-stopper)

More generally:
A great way of speeding up VBA is avoiding loops (or making them more effective) - rather than looping through every cell, we can loop through the hide parameters, something like this:

Sub HideRowsCC()
Dim iProduct As Integer
Application.ScreenUpdating = False
    Rows("7:" & Range("EG9") * 12 + 9).Hidden = False
    Rows(Range("EG9") * 12 + 10 & ":" & 609).Hidden = True
    If Range("EG8") < 12 Then
        For iProduct = 1 To Range("EG9")
            Rows(12 * iProduct + Range("EG8") - 2 & ":" & 12 * iProduct + 9).Hidden = True
        Next iProduct
    End If
Application.ScreenUpdating = True
End Sub

And a personal opinion:
I don't like single-letter-named variables, to easy to typo IMO

Finally, brandnka, this setup is vulnerable to people leaving entries in hidden rows - leading to errors. I recommend you don't use hidden rows at all unless you are also going to write code to exclude the values (!)

HTH

Thread: VBA that hides rows based on reference cell

Thread Tools

Display

VBA that hides rows based on reference cell

Re: VBA that hides rows based on reference cell

Re: VBA that hides rows based on reference cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions