Hide/unhide rows

[karolina1406]

hi All,
I have a problem with the workbook I work on. In one Tab (Tab1) with command buttons (Names A,B,C and D). The second tab is a table with a data for A, B, C and D. I want to hide all rows with 0 in column C in Tab2 if I press command button 1

I have a code which works in the development mode but doesn't work whe nI actually use Command buttons:

Dim r As Long
For r = 3 To 35
If Cells(r, 3) = 0 Then
Rows(r).EntireRow.Hidden = True
Else
Rows(r).EntireRow.Hidden = False
End If
Next r

Any help will be much appreciated.

 

[steve the fish]

Sorry yes what i provided was based on the other macro. It will need altering if you are to use it elsewhere. Ill put some variables in it to make it easier. Hold fire. You can then alter to suit any range.

 

[steve the fish]

Here goes. You need to alter the variables fr, lr and col to suit what you are testing for 0.

fr meaning first row of the test data, lr the last row and col the column number

Dim i As Long, arr, rng As Range, fr As Long, lr As Long, col As Long

fr = 3
lr = 35
col = 3

Application.ScreenUpdating = False

With Sheets("Sheet2")
    .Rows(fr & ":" & lr).Hidden = False
    arr = .Range(.Cells(fr, col), .Cells(lr, col))
    For i = LBound(arr) To UBound(arr)
        If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
            If Not rng Is Nothing Then
                Set rng = Union(rng, .Cells(i + fr - 1, 1))
            Else
                Set rng = .Cells(i + fr - 1, 1)
            End If
        End If
    Next
End With

If Not rng Is Nothing Then rng.EntireRow.Hidden = True

Application.ScreenUpdating = True

 

[karolina1406]

Well, I have done something horribly wrong as it doesn't work at all! I have a command button which I want a code for. What is should then do is to hide all the rows with "0" in Column A in Sheet 1 and all rows with "0" in column C in sheet 2. My complete code is as below:

Dim i As Long, arr, rng As Range, fr As Long, lr As Long, col As Long

fr = 3
lr = 500
col = 3
Application.ScreenUpdating = False
With Sheets("Sheet 1")
.Rows("9:500").Hidden = False
.Range("a7").Value = Sheets("Summary").Range("d7")
arr = .Range(.Cells(3, 3), .Cells(500, 1))
For i = LBound(arr) To UBound(arr)
If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
If Not rng Is Nothing Then
Set rng = Union(rng, .Cells(i + fr - 1, 1))
Else
Set rng = .Cells(i + fr - 1, 1)
End If
End If
Next
End With
If Not rng Is Nothing Then rng.EntireRow.Hidden = True
With Sheets("Sheet 2")
.Rows("3:150").Hidden = False
.Range("e1").Value = Sheets("Summary").Range("d7")
arr = .Range(.Cells(3, 3), .Cells(150, 1))
For i = LBound(arr) To UBound(arr)
If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
If Not rng Is Nothing Then
Set rng = Union(rng, .Cells(i + fr - 1, 1))
Else
Set rng = .Cells(i + fr - 1, 1)
End If
End If
Next
End With
If Not rng Is Nothing Then rng.EntireRow.Hidden = True
Application.ScreenUpdating = True
Sheets("Sheet 1").Select

End Sub

what am I doing wrong??

 

[steve the fish]

That code isnt what i last provided so try using that and altering the fr, lr and col.

 

[karolina1406]

sorry, I am more than a Blonde now. The code you provided works fine if I use it for One sheet only. I thought that I would have to copy and paste and alter this same code for sheet 2 - hence my long code above.

 

[steve the fish]

If you immedaitely want to use it for another sheet then you need to do a bit of cleanup inbetween and change the fr, lr and col for the new sheet. In the example i just used the same sheet twice but gives you a clue

Dim i As Long, arr, rng As Range, fr As Long, lr As Long, col As Long


Application.ScreenUpdating = False

'************first block
fr = 3
lr = 35
col = 3

With Sheets("Sheet2")
    .Rows(fr & ":" & lr).Hidden = False
    arr = .Range(.Cells(fr, col), .Cells(lr, col))
    For i = LBound(arr) To UBound(arr)
        If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
            If Not rng Is Nothing Then
                Set rng = Union(rng, .Cells(i + fr - 1, 1))
            Else
                Set rng = .Cells(i + fr - 1, 1)
            End If
        End If
    Next
End With

If Not rng Is Nothing Then rng.EntireRow.Hidden = True
'***********end of first block

'clean up before next block
Erase arr
Set rng = Nothing

'**********second block
fr = 3
lr = 35
col = 3

With Sheets("Sheet2")
    .Rows(fr & ":" & lr).Hidden = False
    arr = .Range(.Cells(fr, col), .Cells(lr, col))
    For i = LBound(arr) To UBound(arr)
        If arr(i, 1) = 0 And Not IsEmpty(arr(i, 1)) Then
            If Not rng Is Nothing Then
                Set rng = Union(rng, .Cells(i + fr - 1, 1))
            Else
                Set rng = .Cells(i + fr - 1, 1)
            End If
        End If
    Next
End With

If Not rng Is Nothing Then rng.EntireRow.Hidden = True
'*********end of second block

Application.ScreenUpdating = True

It would be possible to do this in a loop if there are lots of sheets but lets not complicate things too much!

 

[karolina1406]

You are a real star. Thank you very much for this. Al works very quick as as I needed...... thank you for your patience with me. I am VA self-taught and I only learn by using correct macros.

 

[steve the fish]

You will get there with practice. Ive never had anyone teach me anything formally about excel and VBA. All done from use and reading examples.