PDA

View Full Version : Hide if certain cell is blank



blackie42
02-21-2008, 07:14 AM
Hi,

Is it possible with use of VBA code to hide rows dependent on the value in the 1st cell in the 1st row?

e.g

I have a table set up (well several) and information is inputted (eventually with a userform) in to certain cells (there are also some formulas etc in it). What I need to be able to do is hide each table unless there is something in the 1st cell in each table.

So first table would be set up as Row A to Row F, G to L etc. All tables are hidden to begin with. If I input something in the userform and point it to A1 it unhides the table and allows other info to be input (probably by userform too) and so on...

Any tips or useful code would be appreciated

thanks

Jon:think:

Simon Lloyd
02-21-2008, 08:34 AM
Use this for starters, try using an array to achieve what you want, but this should give you an idea on how to do it!

Sub hide_if_blank()
Dim MyCell As Range, Rng As Range
Set Rng = Range("A1:A100")
For Each MyCell In Rng
If MyCell = "" Then
MyCell.EntireRow.Hidden = True
End If
Next MyCell
End Sub

Bob Phillips
02-21-2008, 09:44 AM
I think it is a single cell Simon, not one per row.

Jon,

you must know the range, so just use something like



rng.EntireRow.Hidden = rng.Cells(1, 1).Value = ""


although the test seems superfluous to me as you know if it is blank or not, you are populating it!

blackie42
02-21-2008, 11:31 AM
Thanks for replies guys - Yes there are 12 tables sitting on top of each numbered as 'admin groups' 1 thru 12. Some of my funds use only 3 of these e.g. 1, 3, & 8. Some use 4, some use 5 etc. I just wanted to start with all hidden and then as the user inputs info in the userform (e.g textbox 1 = A1 on sheet) then the whole table appears for that group.

thanks again

Jon

Bob Phillips
02-21-2008, 11:33 AM
So does my code help you sort it?

Simon Lloyd
02-21-2008, 01:31 PM
it seems a strange set up!....xld i was going to write some code using Specialcells but i really couldnt fathom what the poster was trying to achieve!

Maybe it would be best hiding all the tables and only showing those populated by the userform?

Bob Phillips
02-21-2008, 01:34 PM
That's my view also Simon.

Simon Lloyd
02-21-2008, 01:39 PM
Apologies for using your sunday name!


xld i was going.......
i was kinda thinkin yeah Bob ...great minds....... or is it fools seldom differ?

Bob Phillips
02-21-2008, 01:55 PM
Oh, no question, it is the latter my fellow fool.

Simon Lloyd
02-21-2008, 02:12 PM
Blackie, give your tables a range name INSERT, NAMES, DEFINE create your name for your table range then in your userform code use something like Range("Table1").Hidden=Trueand of course set it to false to show it....or something along those lines.

The great "El Xid" i agree i have never had a great mind but foolishness i have had a plenty!

blackie42
02-21-2008, 04:20 PM
Thanks again for your guidance - I'm just playing around with a few things at the moment.

Get the idea of naming the tables & hiding them - is it possible to put some code in the workbook module so that on opening it automatically hides the tables and then from the userform inputs unhide a table based on the conditon of the primary cell - as discussed previously?

thanks a lot for your help

Jon

blackie42
02-22-2008, 03:00 AM
Have succesfully hidden the rows on opening the workbook.

Simon Lloyd
02-22-2008, 03:59 AM
Good man!, coding and learning at the same time means lots of trial and error, when you get pointed in the right direction you start conquering things for yourself!