Need help with hiding row under the following circumstances

[Shaolin]

Under the condition if a cell is NOT read "yes", then I want to hide the entire row that cell is on. For instance, if cell k3 does not read "yes" then I want the whole 3rd row to be completely hidden. I recently learned that conditional formatting cannot perform this task. How can I perform this procedure? Do I have write a macro? I never written a macro before and I haven't found ONE website that tells me exactly how to do this. Supposedly it's real easy, but I need some assistence.

I need help fast!!

Thanks in advanced

also, where do I write the macro. Do I go to tools -> macro -> macros, or do I write the code elsewhere?

[Ken Puls]

Hi Shaolin, and Welcome to VBAX! :)

I don't have time to write the macro for you right at the moment, but with regards to the "exactly how to" part... Have a read through the complimentary lesson 1 of our Excel VBA Training (http://www.vbaexpress.com/training.htm) program! It should answer the basic "where" questions for you.

I'm also sure that someone will be along to help you shortly.

Cheers!

[OBP]

The first thing to do for yourself is to record a macro of hiding a row, that will give you the code for hiding a row.

[Zack Barresse]

And if you want a starter macro for you, just make sure you say the range that you want to check.

Such as you want to check A1:A10, if any cell value in this range is "yes", then hide the whole row, you could do this a few ways. Let's look at the following ...

'Ensure variable declaration


Option Explicit
'Test routine #1
'Union (fast)

Sub HideRows_Test1()
'Declare variables.
Dim rngCheck As Range, cel As Range, uCel As Range
'Set range to check for condition ("yes").
Set rngCheck = Range("A1:A10")
'Loop through each cell in the set range.
For Each cel In rngCheck
'Perform condition check on individual cells.
If cel.Value = "yes" Then
'If cells match condition, check if Union exists.
If uCel Is Nothing Then
'If it doesn't exist, create it.
Set uCel = cel
Else
'If it does exist, add to it.
Set uCel = Union(uCel, cel)
End If
End If
Next cel
'Hide all Unioned cells here.
uCel.EntireRow.Hidden = True
End Sub

'Test routine #2
'Standard Loop (faster)
Sub HideRows_Test2()
'Declare variables.
Dim rngCheck As Range, cel As Range
'Set range to check for condition ("yes").
Set rngCheck = Range("A1:A10")
'Loop through each cell in the set range.
For Each cel In rngCheck
'Perform If/Then condition check. If true, perform action.
If cel.Value = "yes" Then cel.EntireRow.Hidden = True
Next cel
End Sub

'Test routine #3
'Array check (fastest)


Sub HideRows_Test3()
'Declare variables.
Dim i As Long, celArr()
'Set range to check for condition ("yes").
celArr = Range("A1:A10")
'Loop through each variable in the array.
For i = LBound(celArr) To UBound(celArr) Step 1
'Perform If/Then condition check. If true, perform action.
If celArr(i, 1) = "yes" Then Range("A" & i).EntireRow.Hidden = True
Next i
End Sub


Hope This Helps :yes

[Shaolin]

Thanks for the responses!


Oh, in addition, the macro that I pasted in the previous post (I'll post it again):


Sub Hidden()
' Hidden Macro
' Macro recorded 4/13/2005 by pdp5
Selection.EntireRow.Hidden = True
Range("A8").Select
End Sub

Is it possible to write the code so that it goes through the whole K column (cell by cell if it has to), and wherever it sees anything other than "Yes" in the K column, it hides the respective rows those cells are on?

I guess that would be the tricky part of the code. Maybe most of the people here are guru's or something.

[OBP]

The other questio to ask yourself is how do you activate it by Macro Button, Automaticaly?
If automaticall when, on start up on cell changes?

[Zack Barresse]

You could adapt one of the examples above to something like this ...


Sub HideRows_Test4()
Dim rngCheck As Range, cel As Range
Set rngCheck = Range("K:K").SpecialCells(xlCellTypeConstants, 23)
For Each cel In rngCheck
If Lcase(cel.Value) <> "yes" Then cel.EntireRow.Hidden = True
Next cel
End Sub

Now this does the opposite of that which I posted above, in the fact that if the cell value is NOT equal to any form of "yes" then that row will be hidden. If this is the opposite of that which you would prefer, replace the 'not equal to' signs ( <> ) with an 'equal to' sign ( = ).


HTH

[Shaolin]

Thanks a lot

One quick question

If I wanted to place the range starting from K3 to the end of the K column, what do I type in the code?

Range ("k3:k")??

[Zack Barresse]

Range("K3", Range("K65536").End(xlUp))
.. or ..

Range("K3:K" & Range("K65536").End(xlUp).Row)
.. or ..

Range("K3:K65536").SpecialCells(xlCellTypeConstants, 23)

[Shaolin]

thanks . . . I'll see if it runs in a few . . .

[Shaolin]

Why 23?

Range("K3:K65536").SpecialCells(xlCellTypeConstants, 23)

[mark007]

You should use the autofilter to hide the rows - much faster than looping through each one ;)

[gsouza]

Sub Hide_rows()
Range("k1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "no" Then 'this may have to be in upper or lower case
Selection.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
End Sub

[Shaolin]

does this make sense to any of you?



If LCase(cel.Value) = "yes" Then cel.EntireRow.RowHeight = 12.75


What I'm basically trying to do is when the cell value reads "yes" I want the row height to be 12.75 or 17 pixels.

[mark007]

To hide and resize your rows use:


Sub HideNonYes()
Application.ScreenUpdating = False
Dim r As Range
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Rows(1).AutoFilter
ws.Rows(1).AutoFilter Field:=11, Criteria1:="=yes"
Set r = Application.Intersect(ws.Cells.SpecialCells(xlCellTypeVisible), ws.UsedRange.Offset(1, 0))
r.EntireRow.RowHeight = 12.75
ws.Rows(1).AutoFilter Field:=11, Criteria1:="<>yes"
Set r = Application.Intersect(ws.Cells.SpecialCells(xlCellTypeVisible), ws.UsedRange.Offset(1, 0))
ws.AutoFilterMode = False
r.EntireRow.Hidden = True
Application.ScreenUpdating = True
End Sub


Should run much faster than the loops above.

Gsouza, you should avoid selecting and activating. Your code can be rewritten as:


Sub Hide_rows()
dim r as range
dim i as long
i=1
set r=activesheet.Range("k1")
Do Until r.offset(i,0).Value = ""
If r.offset(i,0).Value = "no" Then 'this may have to be in upper or lower case
r.offset(i,0).EntireRow.Hidden = True
End If
i=i+1
Loop
End Sub

Hope that helps.

:)

[gsouza]

Thanks 007 I can just never seem to remember all that short cut stuff. But it sure is alot neater and faster. Thanks for the tip. By the way, Shaolin, for someone that never wrote a macro your catching on fast.

[mark007]

No problem gsouza, it's how we all start!

:)

[Zack Barresse]

23: Because there are four different types of Constants we use with this (SpecialCells) method when referring to a cell with values. Each is associated with a Hardcoded constant number. So instead of writing it out, you can use numbers as expressions for which type of constant you would like to make use of. 23 is just the accumulation (you can add them) of all 4 types of constants.

A good (comprehensive) list can be found right here (http://techsupt.windowware.com/TS/T000001033005F9.html). At the bottom there is a link to a zipped file called XL97Constants.zip. This contains a sheet named "MS Excel 97 Constants", from there you can sort either alphabetically or numerically, whichever you prefer.

[mark007]

Alternatively you can look them up in the object browser in the VBIDE. (Hit F2)

:)

[Shaolin]

Thanks a lot folks. I appreciate it. I was too busy with other stuff at work and didn't have the time to show my appreciation. I'll mos def check those links.

Anyway, which VBA book do you recommend and which is the best bang for the buck?

[Zack Barresse]

In no particular order ...

Excel Hacks (http://www.amazon.com/exec/obidos/ASIN/059600625X/ozgridbusines-20/103-8100980-5359852): By Dave Hawley

Excel 2003 Power Programming with VBA (http://www.amazon.com/exec/obidos/tg/listmania/list-browse/-/3P9T4WT9MA29P/103-8100980-5359852): By John Walkenbach

Writing Excel Macros with VBA (http://www.amazon.com/exec/obidos/ASIN/0596003595/exceltip-20/103-8100980-5359852): By Steve Roman

Difinitive Guide to VBA (http://www.amazon.com/exec/obidos/ASIN/1893115798/exceltip-20/103-8100980-5359852): By Michael Kofler

Excel 2002 VBA: Programmer's Reference (http://www.amazon.com/exec/obidos/ASIN/0764543717/ref=pd_sxp_elt_l1/103-8100980-5359852): By Stephen Bullen, Rob Bovey, Josh Green & Robert Rosenberg

Professional Excel Development (http://www.amazon.com/exec/obidos/ASIN/0321262506/ref=pd_sxp_elt_l1/103-8100980-5359852): By Stephen Bullen, Rob Bovey & Josh Green (review here) (http://www.vbaexpress.com/forum/showthread.php?t=2189)

VBA and Macros for Microsoft Excel (http://www.amazon.com/exec/obidos/ASIN/0789731290/ref=pd_sxp_elt_l1/103-8100980-5359852): By Bill Jelen & Tracy Syrstad


Now I've picked up most of these in book stores. They're all good. I own 'VBA and Macros for Microsoft Excel', and it's a good one. My next book will be the 'Professional Excel Development'. A good starter is the Steve Roman book.



HTH

[Ken Puls]

Hi there,

I have only read 2 of the books Zack mentioned. If you're starting out, I'd HIGHLY recommend John Walkenbach's Power Programming one. It's fantastic.

I just picked up the Bullen/Bovey/Green Professional Excel Development, and they specifically say in the forward that they assumet that you've read either their programmer's reference or Walkenbach's power programming.

Fwiw, my copy of Walkenbach's book never left my desk for the first few months. It also comes with the book on PDF as well, so you can keep the CD at work for reference, and read the book at home. I can't say enough about that one. :yes