Macro to hide rows won't work if the first column is blank

[Fabulist]

Hello,

Some time ago WarPigl3t was kind enough to provide me with a macro to hide rows based on range values.

However, if the first column is blank, the macro will not function at all. For example, the values are between column 4 and 6. Columns 5 and 3 are blank while columns 2 has descriptions and column 1 is also blank. The macro will not work if the first column is blank, but will function if I type a number on it, even though the macro is not set to include the first column in the range. The problem goes away if the first row is deleted, and the range goes back to firstColumn =3 and lastColumn = 5 instead of firstColumn =4 and lastColumn = 6.

Sub hideRowMacro()     firstRow = 6
     lastRow = Range("A" & Rows.Count).End(xlUp).Row
     firstColumn = 4
     lastColumn = 6
     r = firstRow
     Do Until r > lastRow
          c = firstColumn
          Do Until c > lastColumn
               myValue = Cells(r, c).Value
               If Round(myValue, 2) <> 0 Then
                    Rows(r).Hidden = False
                    Exit Do
               Else
                    Rows(r).Hidden = True
               End If
               c = c + 1
          Loop
          r = r + 1
     Loop
End Sub

Does anyone know how I can resolve this so I do not have to edit all tables to include a random number on each row on the first blank column?

Thanks in advance!

 

[JackDanIce]

I think the problem is due to how the lastRow is being defined based on column A, see change in code highlighted:

Sub hideRowMacro()

    Dim Firstrow        As Long
    Dim LastRow         As Long
    Dim firstColumn     As Long
    Dim LastColumn      As Long

    Firstrow = 6
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    firstColumn = 4
    LastColumn = 6
    r = Firstrow
    
    Application.ScreenUpdating = False
    
    Do Until r > LastRow
        c = firstColumn
        Do Until c > LastColumn
            myValue = Cells(r, c).Value
            If Round(myValue, 2) <> 0 Then
                Rows(r).Hidden = False
                Exit Do
            Else
                Rows(r).Hidden = True
            End If
            c = c + 1
        Loop
        r = r + 1
    Loop
    
    Application.ScreenUpdating = True
    
End Sub

As alternative, try:

Sub hideRowMacro_v1()

    Dim rng     As Range
    Dim rngData As Range
    Dim rngHide As Range
    Dim LR      As Long
        
    LR = Cells(Rows.Count, 2).End(xlUp).Row - 5
    Set rngData = Cells(6, 4).Resize(LR, 3)
    
    rngData.Rows.Hidden = False
    
    For Each rng In rngData
        If Round(rng.Value, 2) = 0 Then
            Set rngHide = IIf(Not rngHide Is Nothing, Union(rng, rngHide), rng)
        End If
    Next rng
    
    If Not rngHide Is Nothing Then rngHide.entirerows.Hidden = True
    
    Set rngData = Nothing
    Set rngHide = Nothing
    
End Sub

 

[Fabulist]

JackDanIce, changing lastRow's Range to "B" fixed my problem, please excuse my ignorance.

I also see that you added "Dim" definitions to the macro, why did you add them? Also, I see that you added an alternative macro to the one I already have, is there a substantial difference between the two?

Whether you find the time to reply or not, thank you!

 

[JackDanIce]

You're welcome.

Dim is used to define a variable with a name and type - so when the code is complied it allocates an appropriate amount of memory for when it's used. You don't need to do this but it's better programming practise to do so.

The alternative code was for you to try as an alternative(!) as you might find it's faster.

 

[Fabulist]

Thank you for educating me, I will add Dim as you instructed.

Also, I tried your v1 macro, but unfortunately I received an "Invalid procedure call or argument" error, which I have no idea how to resolve.

 

[JackDanIce]

Apologies, didn't test it, try:

Sub hideRowMacro_v1()

    Dim rng     As Range
    Dim rngData As Range
    Dim rngHide As Range
    Dim LR      As Long
        
    Cells.Rows.Hidden = False
        
    LR = Cells(Rows.Count, 2).End(xlUp).Row - 5
    Set rngData = Cells(6, 4).Resize(LR, 3)
    
    For Each rng In rngData
        If Round(rng.Value, 2) = 0 Then
            If Not rngHide Is Nothing Then
                Set rngHide = Union(rng, rngHide)
            Else
                Set rngHide = rng
            End If
        End If
    Next rng
    
    If Not rngHide Is Nothing Then rngHide.EntireRow.Hidden = True
    
    Set rngData = Nothing
    Set rngHide = Nothing
    
End Sub

 

[Fabulist]

Your macro completely hides the entire table without excluding the rows that have numbers (on columns 4 & 6).

 

[JackDanIce]

Missed the Exit Do in the original code. you may need to comment out either or both lines in bold/blue in below:

Sub hideRowMacro_v1()


    Dim rng     As Range
    Dim rngData As Range
    Dim LR      As Long
        
    Cells.Rows.Hidden = False
        
    LR = Cells(Rows.Count, 2).End(xlUp).Row - 5
    Set rngData = Cells(6, 4).Resize(LR, 3)
        
    rngData.EntireRow.Hidden = True
    For Each rng In rngData
        If Round(rng.Value, 2) <> 0 Then
            rng.EntireRow.Hidden = False
            Exit For
        End If
    Next rng
    
    Set rngData = Nothing
    Set rngHide = Nothing
    
End Sub

Otherwise, I may have misunderstood the data in your table.