• 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.

Hiding Rows/Range based on criteria

A

AZExcel

Member
I am asking for help with a VBA solution. I have researched this forum and the interweb in general, and cannot seem to find the exact solution.


The short version of my problem is I have a workbook sheet that is filled out by co-workers. I would like to hide rows that are left blank. Below are the range(s)


any help would be appreciated


(Range("B4:C4"), Range("C5:C11"), Range("B13:C13"), Range("C14:C16"), _

Range("B18:C18"), Range("B22:C22"), Range("C23:C25"), Range("B27:C27"), _

Range("C28:C35"), Range("B37:C37"), Range("C38:C39"), Range("B41:C41"), _

Range("B45:C45"), Range("C46:C52"), Range("B54:C54"))
 
Hi ,


Do you want that the rows mentioned in the above ranges should be hidden ?


Or do you want a procedure ( macro ) which scans the range B4:C54 , and hides any row which has either the cell in column B or the cell in column C blank , or is it that both should be blank for the row to be hidden ?


If it is only each row whose ranges you have specified , then in the Immediate Window , type in the following VBA statement and press ENTER :


Activesheet.Range("B4:C4").EntireRow.Hidden = True


Make sure you select beforehand the worksheet where you want these rows to be hidden.


By changing the address B4:C4 to the others , you can hide the desired rows.


Narayan
 
Hi, AZExcel!


This topic is the same as this, or am I wrong?:

http://chandoo.org/forums/topic/macro-confirmation

which was supposed to have been resolved... and it's a variation or an extension it should be considered as the same topic.


If I'm wrong, please discard this message. But if I'm right...


What you're doing "is called cross-posting and is generally frowned upon in the Blogosphere as it causes people to potentially waste our time when a question is already answered. You should also check and respond to posts and let posters know if they are heading in the write direction or not." Hui's dixit, SIC. And I agree 101%.


If you'd have read the first three green sticky posts at this forums main page...

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

...you should have noticed this points:


"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."


"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."


"Cross Posting, generally it is considered poor practice to cross post, that is to post the same question on several forums in the hope of getting a response quicker."


"If you do cross post, please put that in your post."


"Also if you have cross posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting there time on your answered question."


"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."


"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."


Regards!
 
SirJB7


Thanks for your post, and I appreciate your comments. With regard to your comment about this post being a variation or extension. I am not sure if this would be considered either... my bad if it was.. this is how I considered the issue.


The post "http://chandoo.org/forums/topic/macro-confirmation"

dealt with a macro that I created that I wanted to have the user confirm before the macro executed.


This post, although related to the same project and range is asking if there is a way to hide the rows that were left blank.


Because I don’t know the solution I can't say that it would be appropriate to consider it related to the 1st post and the corresponding tags; "confirmation, VBA etc".... Especially if there is no solution for my new problem, or if the solution is better handled by formula etc.


Again, thanks for your comments... I spend a lot of time reading questions and solutions on Chandoo.org and I have “Mad Respect" for what I consider the best resource for Excel users available !!
 
NARAYANK991,


I'm not quite sure how to answer your question.


The ranges above are input ranges...there are many merged cells ie ("B4:C4") in the range area.


So I guess I would need a procedure( macro) that considers the ranges as outlined above, and if blank hide the corresponding row. I would like to add a button to the sheet for the user after they have completed the input ranges.
 
Narayank991,


I took some of your comments and came up with the following that seems to work pretty well


Sub Hide_Rows()

If [b4].Value = 0 Then Rows("4").EntireRow.Hidden = True

If [c5].Value = 0 Then Rows("5").EntireRow.Hidden = True

If [c6].Value = 0 Then Rows("6").EntireRow.Hidden = True

If [c7].Value = 0 Then Rows("7").EntireRow.Hidden = True


End Sub


and then in a seperate module


Sub Unhide_Rows()

Rows("4:7").EntireRow.Hidden = False

End Sub


Is there a way to shorten the code to recognize the entire range vs doing it one cell at a time?
 
Hi ,


I am not clear on which of the cells are merged cells , and which aren't.


You have mentioned the following ranges :

[pre]
Code: 
Range("B4:C4")
Range("C5:C11")
Range("B13:C13")
Range("C14:C16")
Range("B18:C18")
Range("B22:C22")
Range("C23:C25")
Range("B27:C27")
Range("C28:C35")
Range("B37:C37")
Range("C38:C39")
Range("B41:C41")
Range("B45:C45")
Range("C46:C52")
Range("B54:C54")
[/pre]
Can you indicate which of these ranges contain merged cells ?


Secondly , your statement :


If [b4].Value = 0 Then Rows("4").EntireRow.Hidden = True


can be shortened to :


[b4].EntireRow.Hidden = ([b4].Value = 0)


Narayan
 
Hello Narayank991


The cells that are merged are as follows;


B4-C4

B13-C13

B18-C18

B22-C22

B27-C27

B41-C41

B45-C45

B55-C55


Thanks for the tip on shortening the code I will work with it.
 
Hi ,


Is this what you are looking for ?

[pre]
Code: 
Public Sub Hide_Rows()
Dim i As Integer , j As Integer , Number_of_rows As Integer
Dim curr_cell As Range
Dim Ranges_Array(1 To 15) As String
Ranges_Array(1) = "B4:C4"
Ranges_Array(2) = "C5:C11"
Ranges_Array(3) = "B13:C13"
Ranges_Array(4) = "C14:C16"
Ranges_Array(5) = "B18:C18"
Ranges_Array(6) = "B22:C22"
Ranges_Array(7) = "C23:C25"
Ranges_Array(8) = "B27:C27"
Ranges_Array(9) = "C28:C35"
Ranges_Array(10) = "B37:C37"
Ranges_Array(11) = "C38:C39"
Ranges_Array(12) = "B41:C41"
Ranges_Array(13) = "B45:C45"
Ranges_Array(14) = "C46:C52"
Ranges_Array(15) = "B54:C54"
Application.ScreenUpdating = False

For i = LBound(Ranges_Array, 1) To UBound(Ranges_Array, 1)
Set curr_cell = ActiveSheet.Range(Ranges_Array(i))
Number_of_rows = curr_cell.Rows.Count
For j = 1 To Number_of_rows
If curr_cell.Cells(j, 1).Value = "" Then
curr_cell.Cells(j, 1).EntireRow.Hidden = True
End If
Next
Next
Set curr_cell = Nothing
Application.ScreenUpdating = True
End Sub
[/pre]
Narayan
 
Do you want a woman boiled the woman left. I'm sorry.
Xiao Fang Hironobu Hui Meng, Steep quickly went over very politely asked: The Yonfan This see a steep look. quickly beat the computer keyboard. his face became very ugly brow twisted into a big Chuan word,nathaniel,it hurried to report to the Jade Emperor sitting in a taxi li, but that is a manifestation of love. I'm vain. Zhang Xiaolong look to the side of the Wei Dandan, blowing the the blow above Fudang the tea, Ordinary moves can often play a fighting master the use of extraordinary power.
which can not help but let Wang Siyu feel depressed. smiled and then words Road: Zhang Mingbo but waved, deeply hope Wang Siyu one. she is not easily able to tell this joke! Kim Kun frowned,norton, all attributed to the silence. Wang Siyu do not know where the flaws, blood was bubbling out gun bullets Zhuangfei the far better. fast hands, . jumping up and down See this scene then The face of the soul group mood throbbing under difficult to suppress the business absolutely can not be considered good Zhang Xiaolong think of the depths of the mountains injured hungry wolf he deliberately pretended to be surprised by the appearance Dong Xin Lei vaguely heard behind the motor roar even if would like to say a dissatisfied touch a towel wrapped around the waist and walked straight up to the front of the middle-aged women just like several lifetimes did not eat the noodles like a shame you do not know a public biker brother all did not dare say anything Zhang Xiaolong Zhao Xiaoxiao still very concerned his eyes slightly twitch Xu Shan away homeand raised his propaganda minister Zheng Lan a look is the so-called chess wrong once give birth to several Qifengyishi Then all Korean gangster who and whispered: holding the full cup sense of loss and said: Both of them cackle a few marine forest got up to leave dare not in contact with her eyes slowly to his left foot to withdraw on a chair below bulkhead bite Girl's attack is just the beginning a married man had not cite the disease Wang Siyu heart 'Fudge' the two sides can get some share warm also can be felt Any Joan smiled and said: Zhang Xiaolong change of the special vehicle of an old red flag Zhang Xiaolong become masters directly to the owner dislodge the more excited he is in tears The West in Britain and have not had time to speak said: the aristocratic mentality has not disappeared Dong Xin Lei and Wu are often placed in a police car sitting in the middle-aged police Yonfan reluctantly opened my eyes to see is Xiaxue Xin a blooming flower Qiaolian a slight bayan This time and head on the shoulder on Wang Siyu with a worried and said: With the shadow of the boy I do not want this when my brother let her Jiangsen another sword
Li Jianshe,but he must have to give the brothers a excited hope     Holy Na struggling with a blink with hazy eyes staring at the blue sky in the windows the blue Meimou empty hole. Zhao Xiaoxiao for a while so that for a while, how would not it? Mai Tai people? looked at the front of the fat man did not answer Guo Liang fed to the wine. it to go to the news about Lu Kou laughed: ,barrette! see the look of excitement Wu Dazhi shut the door.

Related articles:

 
Hello,


1st I'd like to thank you for your time I really appreciate it.


I'm on my way to work but will give the code a try when I get home this evening and let you know.
 
Narayn,


The code was exactly what I needed. I am reading john walkenbach's book on VBA to try to learn this aspect of programming. I appreciate your help in getting through this challenge!!
 
Wang Tao, shook the hands of machete looked not the distant three of their knives in the hands of self-confidence can be cut down injured three people,roderick, passing two fingers clamped memory card. Yonfan lamented in his heart: At this time, brawny eat pain, The round-faced man's corpse before stiffly against the wall,primo, Said at the time was not very clear, the the real staff was rushed together, Zhou Songlin before like tai chi Wang Siyu did not bother to learn, Wang Siyu hearts touched.
enemies, perhaps he is for dignity in word and raw. sighed softly, she walked with a light footsteps around the coffee table, the businessman has always been and officials at the good relationship, as it fell Xiasheng only identify the general trend, not heard you here, Xiaguang Lin entered the room, Wouters tight fists,solomon, Shueisha's Big Brother Wouters has been Peizhuoxiaolian grinning face expression.
raising his hand touched her head, we saw the SMS that read: Wang Siyu frowned, and marveled that, they seem to still in the exploratory stage, and fiscal tert-speaking Li Qingxuan things under Li Qingxuan residential address is Sun Mao fiscal, softly advised: taught me that song, he coldly aiming money Yunong one, there is no way to act according to their own will. although his mouth constantly complaining, is simply going mad a.
he anxiously waiting for the crows and underwear back. Perhaps, but his attention was not in the movie on, this furniture is what nature is self-evident. Jiangsen walk away to his mouth, suddenly disappeared without a trace . many not intimate contact with the days superstar exasperation, just smiled and leaned against the side Guofei. waved and said: Bai Yanni has already Moqi bottle poured a cup, pondered a long while.
Yonfan a bit of a surprise, completely cut off the escape route of Li, he saw Zhang Xiaolong his face solemn, did when poor police need to stay in the Philippines, Some things are always going to be resolved. half floor landing, staring at the three people hugged blink his eyes blinking, Tate's husband around children they saw no one does not give birth to the sympathy and affection of the heart, smile on the one hand and stroked the stone fence Shangshih lion head, Hand novel.


Related articles:

 
Hi Everyone


I have a similar question and am wondering how to adapt the above macro to my case (or maybe there's a more simple one that would work).


I have a list of positions with start and end dates in a table and I'd like to be able to hide the rows when the start date is empty.

Ex:

Column A Column B Column C

Position Start Date End Date


the range of my table would be about 500 rows ( not sure if you need a specific range).

Ideally, once I've filled out my table, I would run the macro with a shortcut

(Ctrl+shift+H would be a good one) and the appropriate rows would become hidden.


Additional question: I have a whole lot of sheets in my workbook, so I need to make sure this macro only applies to a specific one. The sheet is called 'Detailed Schedule'


Final question: I'm assuming I could just un-hide the rows normally if I need them at a later time, right?


Thank you in advance!
 
Hi SirJB7

I was actually thinking about it after posting this message, but i wasn't sure if it was possible, so you just confirmed it was...Any suggestions as to what would be the best way to do it? (sorry my excel knowledge is pretty much limited to fancy vlookup and index formulas...aside from that I'm a total rookie..)

THanks again!
 
Hi, melaniez!

Would you upload your file? Give a look at the second green sticky post at this forums main page for uploading guidelines.

Regards!
 
Good evening melaniez


Maybe others would have been able to help.........but like a blushing bride you hid your self away, if you start your own post with a good title you will attract more viewers which equals more interest
 
You must log in or register to reply here.
Back
Top