|
Macro to hide rows not working
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 |
Macro to hide rows not working
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 "Al" wrote: 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 |
Macro to hide rows not working
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" wrote in message ... 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 |
Macro to hide rows not working
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) "Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
Macro to hide rows not working
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" wrote: 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) "Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
Macro to hide rows not working
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" wrote in message ... 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" wrote: 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) "Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
Macro to hide rows not working
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" wrote: 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" wrote in message ... 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" wrote: 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) "Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
Macro to hide rows not working
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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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) "Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
Macro to hide rows not working
Bob, many thanks....I'm off to the bookstore right now!
"Bob Phillips" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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) "Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
Macro to hide rows not working
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 "Bob Phillips" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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) "Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
Macro to hide rows not working
Open the VBE.
Select your workbook/project and right-clickmyworkbook propertiesprotectionlock 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 On Thu, 3 Apr 2008 02:03:02 -0700, Al wrote: 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 "Bob Phillips" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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) "Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
Macro to hide rows not working
Gord, great stuff...thanks so much. Back to my books now!
"Gord Dibben" wrote: Open the VBE. Select your workbook/project and right-clickmyworkbook propertiesprotectionlock 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 On Thu, 3 Apr 2008 02:03:02 -0700, Al wrote: 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 "Bob Phillips" wrote: 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" wrote in message ... 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" wrote: 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" wrote in message ... 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" wrote: 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) "Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
Macro to hide rows not working
I'm a newbie on macro function. here is my file
http://i228.photobucket.com/albums/e...onproblem3.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? "Bob Phillips" wrote: 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" wrote in message ... 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 |
Macro to hide rows not working
This is just what i've been looking for, but I also want blank cells included.
-- Medzzz "Bob Phillips" wrote: 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" wrote in message ... 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" wrote: 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) "Bob Phillips" wrote in message ... 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" wrote in message ... 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 |
Macro to hide rows not working
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 On Mon, 15 Sep 2008 16:39:01 -0700, CevaClerkII <Joel Zurba/NA/TNT/TPG@Logistics wrote: This is just what i've been looking for, but I also want blank cells included. |
| All times are GMT +1. The time now is 01:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com