• 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 with using if condition

sudipto3003

sudipto3003

Member
dear all,

i have an excel sheet where i wants to hide some rows using "if" condition. in cell "c2" if i select "none" then nothing will hide, but if i select "max" or "min" then the row from 8:10 must be hide. i have attached herewith the example sheet below. i am not a vba expert, so if possible please provide your help without vba code.


https://www.dropbox.com/s/zkpcbpx0z89o667/HIDE%20ROWS.xlsx?m
 
Hi,


Create a new module and paste the below code it will work.


Sub hideorunhide()


If Range("c2").Value = "NONE" Then

Rows("8:10").EntireRow.Hidden = False

Range("c2").Select


ElseIf Range("c2").Value = "MAX" Then

Rows("8:10").EntireRow.Hidden = True

Range("c2").Select


ElseIf Range("c2").Value = "MIN" Then

Rows("8:10").EntireRow.Hidden = True

Range("c2").Select


End If

End Sub


Thanks,

Suresh Kumar S
 
thank you suresh for your sugesstion, but as i say i am not a vba expert, what i have done is copy your code from here and paste it at the particular sheet pressing alt+f11. but the result is none. please help me?
 
@Sudipta


Hi


After pressing the Alt+F11 then select the Insert Button from the top and select the Module and just copy the suresh Code and paste in there


or


After Pressing the Alt+F11 then press the short cut key again Alt+i+m then a new module is inserted


Other Wise paste the Below code in the Active Sheet where you want


just right click in the sheet Tab select View code and paste the below code


Private Sub Worksheet_Change(ByVal Target As Range)


If Range("c2").Value = "NONE" Then

Rows("8:10").EntireRow.Hidden = False

Range("c2").Select


ElseIf Range("c2").Value = "MAX" Then

Rows("8:10").EntireRow.Hidden = True

Range("c2").Select


ElseIf Range("c2").Value = "MIN" Then

Rows("8:10").EntireRow.Hidden = True

Range("c2").Select


End If


End Sub


Thanks


SP
 
dear sgmpatnaik,


why the above code is not functioning when i have protected my working sheet with password?


sudipta
 
try


Private Sub Worksheet_Change(ByVal Target As Range)


activesheet.unprotect "Password"

If Range("c2").Value = "NONE" Then

Rows("8:10").EntireRow.Hidden = False

Range("c2").Select


ElseIf Range("c2").Value = "MAX" Then

Rows("8:10").EntireRow.Hidden = True

Range("c2").Select


ElseIf Range("c2").Value = "MIN" Then

Rows("8:10").EntireRow.Hidden = True

Range("c2").Select


End If

activesheet.protect "Password"


End Sub
 
Hi Sudipta,


AS you said you are not a VBA Expert, just turn your data into a table, add helper column and write "Not Hide" against each row you want to, and select filter for "Not Hide", It is done!

[pre]
Code: 
Column1	Column2	Column3	Column4
A	2000	3254	Not Hide
B	5000	6985	Not Hide
C	1500	4215	Not Hide
D	3500	1987
E	4879	3658
F	2458	6420
[/pre]

Faseeh
 
dear indian,


thanks for your help, now its working properly.

also thanks to faseeh, but my requirement was with a condition.


thanks again to every one.
 
@Sudipta


Hi


Sorry for My late replay the reason is two types


1. the code is not Protect and Un protect type, which is Indian has solved


2. with my first code when you are going to protect the sheet then just you have to set the format rows to enable then the code is working


Thanks


SP
 
dear nazmul_muneer


will u mind to show me example please? i have given the link of my example file below..


https://www.dropbox.com/s/zkpcbpx0z89o667/HIDE%20ROWS.xlsx?m


with thanks sudipta
 
dear sgmpatnaik/indian,


i have found one problem using your vba code, i.e, when i have protected my sheet i have allowed to users to use pivot table and auto filter, but using the above code every time automatically those two options coming off, please help me......


sudipta
 
Hi Sudipta,


I will second the opinion of nazmul_muneer, since you have not set a formula based critiera for hiding rows, you will be doing it manually that is which rows should be hiddedn which not to be. You can mark rows that you want to hide and "Hide", every thing else as "Not Hide", when you use filter, you can select "All" for showing every thing, other options can also be selected from it.


I just wrote about it in my last post.


Regards,
 
dear Faseeh,


this is not the answer of my question, if using vba any programme is written in a sheet, where the sheet is protected and allow users to use auto filter and pivot table reports, and the programme is designed in a way that everytime running that programme the sheet will automaticaly password open and closing the programme again the sheet protect with password(as shown above). my question is why after run the programme when my sheet is again password protected, the auto filter & use pivot table becoming uncheck? is there any solution for that?


sudipta
 
Hi Sudipta,


Instead of

Code: 
activesheet.protect "Password"


use below line..

[pre][code]ActiveSheet.Protect "Password" _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
You can try any of the below tag.. by True / False.. if some more feature you want to provide to the user..


DrawingObjects
Contents
Scenarios
UserInterfaceOnly
AllowFormattingCells
AllowFormattingColumns
AllowFormattingRows
AllowInsertingColumns
AllowInsertingRows
AllowInsertingHyperlinks
AllowDeletingColumns
AllowDeletingRows
AllowSorting
AllowFiltering
AllowUsingPivotTables[/code][/pre]
Regards,

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