Hide/unhide rows

karolina1406

Board Regular
Joined
Apr 18, 2016
Messages
101
Office Version
365
Platform
Windows
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.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,080
Office Version
365
Platform
Windows
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:

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,864
Office Version
365
Platform
Windows
Another option is
Code:
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

Board Regular
Joined
Apr 18, 2016
Messages
101
Office Version
365
Platform
Windows
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

Board Regular
Joined
Apr 18, 2016
Messages
101
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,864
Office Version
365
Platform
Windows
What are the other buttons doing?
 

karolina1406

Board Regular
Joined
Apr 18, 2016
Messages
101
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,864
Office Version
365
Platform
Windows
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

Board Regular
Joined
Apr 18, 2016
Messages
101
Office Version
365
Platform
Windows
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):
Code:
[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]
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,864
Office Version
365
Platform
Windows
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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,102,711
Messages
5,488,450
Members
407,638
Latest member
brandynl

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top