mads
|
| posted on 30/9/11 at 12:41 PM |
|
|
anyone know how to hide rows or minimise group based on cell value (Excel)
hey up all,
I think the sun is getting to me a little and cant work out how to write the VBA code to do the following:
If cell value of H10 = Y
then rows 14 - 24 are visible
If cell value of H10 = N
then rows 14 - 24 are hidden
I can group the rows together if that makes it easier and then the group just needs to be minimised based on the users value in cell H10
Cheers,
mads
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
|
|
mcerd1
|
| posted on 30/9/11 at 12:56 PM |
|
|
my memory of VBA is not so good (its been more than 8 years since I had to use it)
but I aways uesd to record a macro on a dummy sheet/woorkbook - steal the bit of code and modify it to suit
so I've just recorded a wee macro of me hideing a selected row / column and this is what I got:
Rows("28:29").Select
Selection.EntireRow.Hidden = True
Columns("H:J").Select
Selection.EntireColumn.Hidden = True
so you just need to get it to sellect the rows when your condition is met and use the command above to hide the selection.... (if x = y then select
row z and so on...... )
[Edited on 30/9/2011 by mcerd1]
-
|
|
|
MikeRJ
|
| posted on 30/9/11 at 01:07 PM |
|
|
Excuse the stupid 'code' formatting - I wish Chris would disable double spacing and make it ignore VB code like smileys.
code:
Sub HideRows()
Dim i As Integer
Dim hideState As Boolean
hideState = (UCase(Cells(10, 8).Value) = "Y" )
For i = 14 To 24
Cells(i).EntireRow.Hidden = hideState
Next
End Sub
[Edited on 30/9/11 by MikeRJ]
|
|
|
Tiger Super Six
|
| posted on 30/9/11 at 01:08 PM |
|
|
If it's that simple (i.e. 'Y' and 'N' just put on auto filter and then select only 'Y' or 'N'
as required?
Mark
Tiger Avon
|
|
|
mcerd1
|
| posted on 30/9/11 at 01:10 PM |
|
|
Mike's one should work better than my stupid one 
it avoids the whole selection carry-on
[Edited on 30/9/2011 by mcerd1]
-
|
|
|
MikeRJ
|
| posted on 30/9/11 at 01:16 PM |
|
|
Actualy mines pretty stupid now I've thought about it, using ranges is a lot simpler!
code:
Sub HideRows()
Range("A14:A24" ).EntireRow.Hidden = (UCase(Range("H10" ).Text) = "Y" )
End Sub
Excuse the random spaces I have to put into the code to stop the stupid forum converting some of it to smileys. It's thoroughly broken in this
area 
|
|
|
mcerd1
|
| posted on 30/9/11 at 01:20 PM |
|
|
^^ you could just tick the "Disable Smilies" box...
going marganaly off topic - does anyone have any idiots guide websites / books they'd recommend for someone to learn VBA ?
(I'm going to have to use it again soon, but I can hardly remember any of it )
[Edited on 30/9/2011 by mcerd1]
-
|
|
|
MikeRJ
|
| posted on 30/9/11 at 01:30 PM |
|
|
quote:
Originally posted by mcerd1
^^ you could just tick the "Disable Smilies" box...
That disables smilies for the entire post AFAIK? The forum is supposed to leave any text in a [ code ] block unformatted, so it's is genuinely
broken at the moment.
There is a VBA For Dummies book that is pretty good, and the "VBA Power Programming" has just been recommended by a colleague.
You can get a reasonable idea of stuff by recording macros and then looking at the source code, and there are thousands of examples on the web if you
get stuck on a particular point.
[Edited on 30/9/11 by MikeRJ]
|
|
|
Minicooper
|
| posted on 30/9/11 at 01:33 PM |
|
|
My turn
Sub HideUnhideRows()
If UCase(Range("H10" ).Value) = "Y" Then
Rows("14:24" ).EntireRow.Hidden = False
End If
If UCase(Range("H10" ).Value) = "N" Then
Rows("14:24" ).EntireRow.Hidden = True
End If
End Sub
David
|
|
|
jase380
|
| posted on 30/9/11 at 05:11 PM |
|
|
Christ, you lost me with the title !!
|
|
|
r1bob
|
| posted on 30/9/11 at 08:49 PM |
|
|
quote:
Originally posted by jase380
Christ, you lost me with the title !!
|
|
|
mads
|
| posted on 9/10/11 at 01:44 PM |
|
|
thanks all. worked a treat 
We gain knowledge faster than we do wisdom!
Life is not a journey to the grave with the intention of arriving safely in a pretty and well-preserved body, but rather to skid in
sideways, thoroughly used, totally worn out and loudly proclaiming... "f*ck, what a trip!"
|
|
|
