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]

If r = 1 which row 1 are you refering to? Every sheet has a row 1. You have to tell excel which row. For example use a with statement:

Dim r As Long
With Sheets("Sheet1")
    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
End With

The reason it worked in testing is that you would have been on the correct sheet and if the sheet is not included in Rows, Range, Cells etc then excel ,by default, will use the active sheet.

 

[Fluff]

Another option is

Sub karolina1406()
   Dim Rw As Long
   With Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")
      For Rw = 3 To 35
         .Rows(Rw).Hidden = .Cells(Rw, 3) = 0
      Next Rw
   End With
End Sub

Change value in red to suit.

 

[karolina1406]

thank you for this. I applied this code to the command button A and B code which is in Tab1. but I specified that hidden rows must be in Tab 2. It works ok for the first "Click" e.g. when all the rows in Tab2 are unhidden and I click Command Button A n Ta1, but then, when I come back to Tab1 to click Command button B - it doesn't hide any new rows

 

[karolina1406]

thank you for this. I applied this code to the command button A and B code which is in Tab1. but I specified that hidden rows must be in Tab 2. It works ok for the first "Click" e.g. when all the rows in Tab2 are unhidden and I click Command Button A n Ta1, but then, when I come back to Tab1 to click Command button B - it doesn't hide any new rows

ok, just realised that I would have to click Required command button twice to relevant rows be hidden. Any idea why?

 

[Fluff]

What are the other buttons doing?

 

[karolina1406]

What are the other buttons doing?

This same function. Basically, I want a user to open the spreadsheet and he/se will land on the page with 3 (for now) Command buttons: John, Anna, Chis. Tab2 is a table were I have a list of courses each of them. I want them to see only courses relevant to them. When John clicks "John" command button on Tab1 - I want him to be taken to Tab2 and the table to display rows with only John's course.

 

[Fluff]

In that case can you please post the code you are using.
Originally you wanted to hide rows that had a 0 in col C, but that doesn't seem to tie with what you are now saying.

 

[karolina1406]

In that case can you please post the code you are using.
Originally you wanted to hide rows that had a 0 in col C, but that doesn't seem to tie with what you are now saying.

sorry for confusion. I applied supporting measure in col C as exce formula: if($D$3=$e4 ,1,0). Hence I was filtering column C. Whole Command Button cod for John (CB1):

[FONT=Calibri][SIZE=3][COLOR=#000000]Application.Calculation = xlCalculationManual[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.ScreenUpdating = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.DisplayStatusBar = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.EnableEvents = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim xls As Excel.Application[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim wkb As Excel.Workbook[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim wks(1 To 12) As Excel.Worksheet[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set xls = Excel.Application[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set wkb = xls.ThisWorkbook[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set xls = Excel.Application[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set wkb = xls.ThisWorkbook[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set wks(1) = wkb.Sheets("Welcome")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Set wks(2) = wkb.Sheets("Courses")[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("Welcome").Visible = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("Courses").Visible = False[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]wks(2).Unprotect "kk"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]wks(2).Range("e1").Value = wks(1).Range("d7")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Dim r As Long[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]With Sheets("Courses")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]For r = 3 To 35[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    .Rows(r).EntireRow.Hidden =False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]If .Cells(r, 3) = 0 Then[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    .Rows(r).EntireRow.Hidden =True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Next r[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]wks(2).Protect "kk"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]ActiveSheet.DisplayAutomaticPageBreaks = False[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.ScreenUpdating = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.EnableEvents = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.DisplayStatusBar = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.ScreenUpdating = True[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Application.Calculation = xlCalculationAutomatic[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

 

[Fluff]

The reason you had to click the button twice, is col C has a formula, but you have turned calculation off.
You also have a lot of redundant code. Try

Sub karolina1406()
   Dim Ws As Worksheet
   Dim Rw As Long
   
   Set Ws = Sheets("Welcome")
   Ws.Visible = True
   
   With Sheets("Courses")
      .Visible = False
      .Unprotect "kk"
      .Range("E1").Value = Ws.Range("d7")
   
      For Rw = 3 To 35
         .Rows(Rw).Hidden = .Cells(Rw, 3) = 0
      Next Rw
      .Protect "kk"
   End With
End Sub