• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hide rows using a command button

RobSA

RobSA

Member
Hi folks,


I have a spreadsheet that has a column with values 1 or 0 and I would like to create a command button that if selected, all the rows in the column F - having a vaule of one (1) would then be hidden.


Similarly if the show button is selected all the rows would then be visible again.


I look forward to your replies
 
Hi RobSA,


I dont know much about macros but maybe this code will help.

First insert a command button(activeX control) and then right click and choose "view code". you will see a Subroutine has already been added.Write the code below between "Sub" and "End Sub" :


ActiveSheet.Range("F:F").AutoFilter Field:=1, Criteria1:="0"


Now repeat the steps and add another command button and write this code:


ActiveSheet.Range("F:F").AutoFilter Field:=1


The first button is for filtering value "1" in column F, and the second button is for showing all the values.

Make sure to turn off the "design mode" when clicking on the command buttons.
 
Hi Rob,


Can you please download the below and check.


http://www.2shared.com/document/CIC0VQwR/1_online.html


Thanks,

Suresh Kumar S
 
Hi Suresh,


Thanks for the help - the idea is perfect.


How do I incorporate it to my spreadsheet ?


Regards

Rob
 
Hi Rob,


Press Alt+F11 to view the macro code on the left side you can see VBAProject (1.xls)

below that sheet1(sheet1), thisworkbook and modules. Double click the sheet1 you can find the macro codes for both command button copy those code and paste it in your workbook (paste the code to the corresponding sheet you have the data) and create two command buttons with the name of CommandButton1 & CommandButton2. It will work


Thanks,

Suresh Kumar S
 
Hi Suresh,


Thanks for the help but I want to use a formula that looks like this and try move away form the autofilter option.


Private Sub Worksheet_Change(ByVal Target As Range)


Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = False


With ActiveSheet

For Each cell In Range("F12:F19,F24:F47")

If cell.Value = 1 Then

cell.EntireRow.Hidden = True

ElseIf cell.Value = 1 Then

cell.EntireRow.Hidden = False

End If

Next

End With


Application.ScreenUpdating = True

End Sub


Sub Macro1()

'

' Macro1 Macro

'


'

End Sub


Not sure if I have it right in terms of setting the rannge.


If the row has the value =1 then it will hide the row.


Any advice to get this towork will be great
 
Hi SirJB7,


I have thanks - that is the autofilter option.


The other option is difficult to do as I have many rows and to do the record option for the rather long worksheet will also not work.


Thanks for the prompt response
 
Hi, RobSA!

I think including the posted codes for two command buttons it should be the easiest and fastest solution.

Just advise if any issue.

Regards!
 
Hi SirJB7,


Thanks for the advice.


The autofilter does work but it filters already hidden rows and the unhide option opens up the unhidden rows which I do not want.


the idea is to get all the rows that are not to be hidden to appear on a seperate sheet for reporting and printing purposes.


So this will allow me to set the value of 1 or 0 and the report on the seprate page will be automatically generated.


If I use the post suggested I do not have this functionality.


I appreciate the advice and look forward to continued ideas


Regards
 
@RobSA

HI


It is better to upload a sample file to get a better assistance from us


as per my understanding on your code you want to hide that type of rows which are greater than 0 OR <>= 1 then simple you make change the code


Please download the below file you don't need to create a filter just i add a command button for hide rows to un hide


http://www.2shared.com/document/Cto62UhT/Book1.html


i think it will solve your problem other wise please refer us with a sample workbook


Thanks


SP
 
Hi sgmpatnaik,


Thanks for your help.


The file you loaded unfortunalty does not work and has no macro attached to the button -or perhaps none that I can find.


Perhaps you can advise if the macro above is a functionalmacro - as I do not know how to write the macro at all.


Regards


Regards
 
@RobSa

Hi


I Think i upload a wrong file so i download the file and check after checking i upload the correct file any how i will give you the commands to create a macro first of all let me know which version office you are using


Thanks


SP
 
@RobSa


Thanks for your information


in the ribbon you can see a Developer Option Click That Option


now follow the steps


1. In Control Option you will find Insert option


2. Click the Insert Option there you will find two option 1. Form Control 2. Active X Control


3. In the Active X Control you can C the Command Button Click and drag where you like in your Sheet


4. Right Click the Command Button there you will find Command Button Object there you can edit the Command Button Name "REPLACE THE NAME WITH UNHIDE ROWS"


5. Click any blank cell in the sheet again Double Click the Command Button then you will go direct to VB Code Mode


6. Paste the Below Code


Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = False


With ActiveSheet

For Each cell In Range("F12:F19,F24:F47")

If cell.Value = 1 Then

cell.EntireRow.Hidden = False

End If

Next

End With


Application.ScreenUpdating = True


Other Wise Try to download the file again from the below Link


https://www.dropbox.com/s/yjdeyqnh33rhq6a/Book1.xls


Thanks


SP
 
Hi SP,


Thanks for you efforts and help it is appreciated.


Please find my file at this link https://www.dropbox.com/s/si1e2o2i0on1p6y/Quality%20Dashboard%20-%20Master%20File.xlsm


The work sheet I am working with is QMS IMP


The idea is to hide the row which has the value 1 in Column F. There are hidden rows and to select the full column will not be effective, hence me providing the first two ranges in my previous example.


I would be gratefull of your help to get this to work.


Regards
 
@RobSA

Hi


i downloaded your file but the macro's not working in my office system


as my understanding your required is that in the column F is there any Number is Equal to 1 then the row to be hide wright


then you just add the below code for hide the rows


Sub HideRows()

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = False


With ActiveSheet

For Each cell In .Range("F:F")

If cell.Value = 1 Then

cell.EntireRow.Hidden = True

ElseIf cell.Value = 1 Then

cell.EntireRow.Hidden = False

End If

Next

End With


Application.ScreenUpdating = True


End Sub


and for hidden rows you can add the below code


Sub Show_Rows()

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = False


With ActiveSheet

For Each cell In Range("F:F")

If cell.Value = 1 Then

cell.EntireRow.Hidden = False

End If

Next

End With


Application.ScreenUpdating = True

End Sub


if still you are facing problem then please refer us again


Thanks


SP


Note: It's is better to use the Command Buttons in Active X Control
 
Hi SP,


Thanks for you help - the code works well.


I have found that there is one challeneg in this regard and that is if there is a cell that is merged say F10 with F11 then the macro doesnt hide the row in F10 but carries on with the remainder.


The process also seems to be rather slow and that is perhaps because it is looking at the entire column.


Do you have a suggestion to fix these two chalenges.


Regards

ROb
 
You must log in or register to reply here.
Back
Top