VBA code stops execution without error after hiding rows

Question

I'm having quite the struggle with some code that used to work just fine. In one of my subs, I hide a bunch of rows (1599 to be exact), then unhide the ones I need, usually around 200. Check out the code:

Private Sub HideUnneededRows(numberToShow As Integer)
Dim thisSheet As Worksheet
Dim allHideableRows As String, rowsToShow As String
Dim firstRow As Integer
Dim secondRow As Integer
Dim lastRow As Integer
Dim lastRowToShow As Integer

    Set thisSheet = ThisWorkbook.Sheets(1)

    firstRow = thisSheet.Range("mass1").row
    secondRow = firstRow + 1
    lastRow = firstRow - 1 + maxNumberOfRows
    lastRowToShow = firstRow - 1 + numberToShow '//numberToShow is usually 200

    '//THIS NEXT LINE RUNS, BUT STOPS EXECUTING AFTER THE ACTION IS PERFORMED
    Range("A" + CStr(secondRow) + ":A" + CStr(lastRow)).EntireRow.Hidden = True 
    Range("A" + CStr(secondRow) + ":A" + CStr(lastRowToShow)).EntireRow.Hidden = False

End Sub

All is well and good until the code hides the rows (first of the "Range("A" +....).Hidden" lines). It performs the hide action just fine, but then doesn't reach the next line of code, which would be to un-hide a different set of rows. Things I've tried:

• Hard-coding the range string (i.e. Range("A25:A1623") instead of Range("A" + CStr(secondRow) + ":A" + ....))
• Performing these two hide actions through the Rows object (i.e. Rows("25:1623").Hidden = True)
• Extracting those two hide lines into their own button_click event in a new module to see if it was something about the module it was currently in

All of these have yielded the exact same result - the code stops all execution after the 1599 rows are hidden.

Help?!

Answer 1

Thanks to @PeterT, the solution to my problem was to turn off screen updating before running the code, then turning it back on after the code finished running. The new code is as such:

Private Sub HideUnneededRows(numberToShow As Integer)
Dim thisSheet As Worksheet
Dim firstRow As Integer
Dim secondRow As Integer
Dim lastRow As Integer
Dim lastRowToShow As Integer

    Set thisSheet = ThisWorkbook.Sheets(1)

    firstRow = thisSheet.Range("mass1").row
    secondRow = firstRow + 1
    lastRow = firstRow - 1 + maxNumberOfRows
    lastRowToShow = firstRow - 1 + numberToShow '//numberToShow is usually 200

    '//Turn off ScreenUpdating
    Application.ScreenUpdating = False
    '//THIS NEXT LINE WAS GIVING ME TROUBLES
    Range("A" + CStr(secondRow) + ":A" + CStr(lastRow)).EntireRow.Hidden = True 
    Range("A" + CStr(secondRow) + ":A" + CStr(lastRowToShow)).EntireRow.Hidden = False
    '//Turn ScreenUpdating back on
    Application.ScreenUpdating = True

End Sub

Answer 2

The following code snippets used for debugging purpose works just fine (hide/unhide rows)

Private Sub HideUnneededRows()
Dim thisSheet As Worksheet
Dim allHideableRows As String, rowsToShow As String
Dim firstRow As Integer
Dim secondRow As Integer
Dim lastRow As Integer
Dim lastRowToShow As Integer

'for debugging purpose
numberToShow = 200
maxNumberOfRows = 300
'--------------------

    Set thisSheet = ThisWorkbook.Sheets(1)

    'firstRow = thisSheet.Range("mass1").Row
    ' for debugging purpose
    firstRow = 1
    secondRow = firstRow + 1
    lastRow = firstRow - 1 + maxNumberOfRows
    lastRowToShow = firstRow - 1 + numberToShow '//numberToShow is usually 200

    '//THIS NEXT LINE RUNS, BUT STOPS EXECUTING AFTER THE ACTION IS PERFORMED
    Range("A" + CStr(secondRow) + ":A" + CStr(lastRow)).EntireRow.Hidden = True
    Range("A" + CStr(secondRow) + ":A" + CStr(lastRowToShow)).EntireRow.Hidden = False

End Sub

Check all your "magic numbers" like maxNumberOfRows variable (set to 300 for debugging purpose: where it comes from/declared?) and the firstRow from that "mysterious" Range "mass1".

Note: in VBA code, the strings concatenation is typically done with ampersand "&", but this syntax will work fine as well:

Range("A" + CStr(secondRow) + ":A" + CStr(lastRow))

Hope this will resolve your issue.

Answer 3

Looks like you are from C#/Java side - using + to concatenate strings, in VBA, use ampersand &.

Assuming maxNumberOfRows is a global variable or a function, try below.

Private Sub HideUnneededRows(numberToShow As Integer)
    Dim firstRow As Long, secondRow As Long
    Dim lastRow As Long, lastRowToShow As Long
    Dim lCalcMode As Long

    firstRow = ThisWorkbook.Names("mass1").RefersToRange.Row
    secondRow = firstRow + 1
    lastRow = firstRow - 1 + maxNumberOfRows
    lastRowToShow = firstRow - 1 + numberToShow '//numberToShow is usually 200

    lCalcMode = Application.Calculation
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Rows(secondRow & ":" & lastRow).Hidden = True
    Rows(secondRow & ":" & lastRowToShow).Hidden = False
    Application.Calculation = lCalcMode
    Application.ScreenUpdating = True
End Sub

Changed rows related data type to Long (saving Excel do conversion).