Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to hide rows
I have formulas in consecutive rows, but I want to run a macro that will hide
the rows that equal 0, and if they change and don't equal 0, the row will unhide. Is there a code for this? (I'm using Excel 2003) Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to hide rows
On Fri, 11 Jul 2008 08:15:01 -0700, lightbulb
wrote: I have formulas in consecutive rows, but I want to run a macro that will hide the rows that equal 0, and if they change and don't equal 0, the row will unhide. Is there a code for this? (I'm using Excel 2003) Thanks! Try this: Private Sub Worksheet_Change(ByVal Target As Range) maxrownumber = 100 column_to_test = 1 For i = 1 To maxrownumber If Cells(i, column_to_test).HasFormula Then Rows(i).EntireRow.Hidden = (Cells(i, 1) = 0) End If Next i End Sub You can play with the maxrownumber and column_to_test to suit your needs. Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to hide rows
On Fri, 11 Jul 2008 16:08:11 GMT, Lars-Åke Aspelin
wrote: On Fri, 11 Jul 2008 08:15:01 -0700, lightbulb wrote: I have formulas in consecutive rows, but I want to run a macro that will hide the rows that equal 0, and if they change and don't equal 0, the row will unhide. Is there a code for this? (I'm using Excel 2003) Thanks! Try this: Private Sub Worksheet_Change(ByVal Target As Range) maxrownumber = 100 column_to_test = 1 For i = 1 To maxrownumber If Cells(i, column_to_test).HasFormula Then Rows(i).EntireRow.Hidden = (Cells(i, 1) = 0) End If Next i End Sub You can play with the maxrownumber and column_to_test to suit your needs. Hope this helps / Lars-Åke Oops, forgot to change one of the 1's to column_to_test. Private Sub Worksheet_Change(ByVal Target As Range) maxrownumber = 100 column_to_test = 1 For i = 1 To maxrownumber If Cells(i, column_to_test).HasFormula Then Rows(i).EntireRow.Hidden = (Cells(i, column_to_test) = 0) End If Next i End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to hide rows
This did not work. In excel, I went to Tools-Macro-and named a new macro,
then I simply copy and pasted your macro into the Visual Basic code and it did not work. Am I doing something wrong? Thanks! "Lars-Ã…ke Aspelin" wrote: On Fri, 11 Jul 2008 16:08:11 GMT, Lars-Ã…ke Aspelin wrote: On Fri, 11 Jul 2008 08:15:01 -0700, lightbulb wrote: I have formulas in consecutive rows, but I want to run a macro that will hide the rows that equal 0, and if they change and don't equal 0, the row will unhide. Is there a code for this? (I'm using Excel 2003) Thanks! Try this: Private Sub Worksheet_Change(ByVal Target As Range) maxrownumber = 100 column_to_test = 1 For i = 1 To maxrownumber If Cells(i, column_to_test).HasFormula Then Rows(i).EntireRow.Hidden = (Cells(i, 1) = 0) End If Next i End Sub You can play with the maxrownumber and column_to_test to suit your needs. Hope this helps / Lars-Ã…ke Oops, forgot to change one of the 1's to column_to_test. Private Sub Worksheet_Change(ByVal Target As Range) maxrownumber = 100 column_to_test = 1 For i = 1 To maxrownumber If Cells(i, column_to_test).HasFormula Then Rows(i).EntireRow.Hidden = (Cells(i, column_to_test) = 0) End If Next i End Sub |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to hide rows
Right click the sheet tab where you want this macro and select view code,
paste it there -- Regards, Peo Sjoblom "lightbulb" wrote in message ... This did not work. In excel, I went to Tools-Macro-and named a new macro, then I simply copy and pasted your macro into the Visual Basic code and it did not work. Am I doing something wrong? Thanks! "Lars-Åke Aspelin" wrote: On Fri, 11 Jul 2008 16:08:11 GMT, Lars-Åke Aspelin wrote: On Fri, 11 Jul 2008 08:15:01 -0700, lightbulb wrote: I have formulas in consecutive rows, but I want to run a macro that will hide the rows that equal 0, and if they change and don't equal 0, the row will unhide. Is there a code for this? (I'm using Excel 2003) Thanks! Try this: Private Sub Worksheet_Change(ByVal Target As Range) maxrownumber = 100 column_to_test = 1 For i = 1 To maxrownumber If Cells(i, column_to_test).HasFormula Then Rows(i).EntireRow.Hidden = (Cells(i, 1) = 0) End If Next i End Sub You can play with the maxrownumber and column_to_test to suit your needs. Hope this helps / Lars-Åke Oops, forgot to change one of the 1's to column_to_test. Private Sub Worksheet_Change(ByVal Target As Range) maxrownumber = 100 column_to_test = 1 For i = 1 To maxrownumber If Cells(i, column_to_test).HasFormula Then Rows(i).EntireRow.Hidden = (Cells(i, column_to_test) = 0) End If Next i End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to hide rows not working | Excel Discussion (Misc queries) | |||
Macro/code to hide rows | Excel Worksheet Functions | |||
Macro that will unhide then hide rows | Excel Discussion (Misc queries) | |||
hide rows with macro | Excel Discussion (Misc queries) | |||
macro to hide rows | Excel Discussion (Misc queries) |