Board logo

anyone know how to hide rows or minimise group based on cell value (Excel)
mads - 30/9/11 at 12:41 PM

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

mcerd1 - 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 - 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 - 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?

mcerd1 - 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 - 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 - 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 - 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 - 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 - 30/9/11 at 05:11 PM

Christ, you lost me with the title !!

r1bob - 30/9/11 at 08:49 PM

quote:
Originally posted by jase380
Christ, you lost me with the title !!

mads - 9/10/11 at 01:44 PM

thanks all. worked a treat