karolina1406

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

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
 
Upvote 0
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.
 
Upvote 0
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 :(
 
Upvote 0
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?
 
Upvote 0
What are the other buttons doing?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top