Macro to hide rows not working

[Al]

What I'm trying to achieve is the automatic hiding of entire rows if the
value of a cell, in this case in column B, is < 1. The below example
works...row 6 is hidden.

Sub hide_row()
If Range("B6").Value < 1 Then Range("B6").EntireRow.Hidden = True
End Sub

But when I try to impose this upon a range of column B, then, alas, not the
desired result!

If Range("B6,B9").Value < 1 Then Range("B6,B9").EntireRow.Hidden = True

Can anyone point me in the right direction? Thanks in advance!

Al

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in

Sub sonic()
LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set myrange = Range("B1:B" & LastRow)
For Each c In myrange
If c.Value <> "" And c.Value < 1 Then
c.EntireRow.Hidden = True
End If
Next
End Sub

Mike

 

[Bob Phillips]

Sub hide_row()
For Each cell In Range("B6,B9")
cell.EntireRow.Hidden = cell..Value < 1
Next cell
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Too many dots

Sub hide_row()
For Each cell In Range("B6,B9")
cell.EntireRow.Hidden = cell.Value < 1
Next cell
End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Al]

Bob, this seems to work for cells B6 and B9 only...but not the range. i.e. I
also have B7<1, but this row is not hidden.
Thanks for your help!

 

[Bob Phillips]

I thought that was how you wanted it Al <g>.

Try

Sub hide_row()
For Each cell In Range("B6:B9")
cell.EntireRow.Hidden = cell.Value < 1
Next cell
End Sub



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Al]

Bob, many thanks. I'll give this one a go first thing in the morning once I'm
back in the office.

Can you advise me a good place to start learning all this "VBA macro stuff"?!

Al

 

[Bob Phillips]

Al,

I would suggest 3 things.

First, get yourself a reasonably ambitious mid-sized project that will
actually be useful to you. Something like a building a home accounts
application. Work out what it needs to do, the inputs and outputs and
document that in some for.

Get a good VBA book. John Walkenbach's are usually reliable, VBA for Dummies
or some other title. Go to a decent bookstore, browse and see who writes in
a style that you are comfortable with.

Then develop it. Read some of the book, practice the techniques. Then break
your earlier design down into chunks, say one for inputting cheques, one for
standing orders, and use what you have learned to develop those chunks.If
you get stuck, dive into the book, or come visit us again. Then move on to
the next chunk etc.


You will soon improve.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Al]

Bob, many thanks....I'm off to the bookstore right now!

 

[Al]

Bob,

Have bought my books.....

One thing I need to know quickly...is it possible to "hide" the macro code
from others. I'm now using the above code that you helped me with, but
already people in my staff are messing around with it! I'd like to hide it
from view to avoid this temptation! Any suggestions please?

Al

 

[Gord Dibben]

Open the VBE.

Select your workbook/project and right-click>myworkbook
properties>protection>lock project for viewing. Add a password and OK out.

Note: file must be saved, closed and re-opened before protection takes place.


Gord Dibben MS Excel MVP

 

[Al]

Gord, great stuff...thanks so much. Back to my books now!

 

[stylissimo]

I'm a newbie on macro function. here is my file >>>
http://i228.photobucket.com/albums/ee183/o0wearhouse0o/excel_functionproblem3.jpg

i want to create a macro that would automatically hide the blank cells (the
cells cannot be deleted because it has formula)

how do i go about it?

 

[CevaClerkII]

This is just what i've been looking for, but I also want blank cells included.

 

[Gord Dibben]

Sub hide_row()
For Each cell In Range("B6:B9")
With cell
If .Value < 1 Or .Value = "" Then
.EntireRow.Hidden = True
End If
End With
Next cell
End Sub


Gord Dibben MS Excel MVP