Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
hiding rows using macro
Sub hideifzeros()
Dim a as Range For Each a In Range(Cells(1,1),Cells(rows.count,1).End(xlup)) If a = 0 And a.Offset(0, 3) = 0 Then a.EntireRow.Hidden = True Next End Sub column D is offset 3 from Column A. -- Regards, Tom Ogilvy "Greg Stevens" wrote in message ... Hello, I am looking for some assistance here with a code I am trying to write for an excel data. Here is the scenario: I have 10 columns (A:J) with about 1000 rows. Each of those cells within the 10 columns and 1000 rows contain numbers. We want to run a macro in which for columns A and D, when and only if they both equal "0"then we want to hide that specific row and continue onto the next row and keep on hiding if the columns A and D both equal "zero" until the entire sheet has been searched. So far here is what I have but the macro is just taking the first row and hiding regardless of values and stopping the Sub hideifzeros() For Each a In Selection If a = 0 And a.Offset(0, 4) = 0 Then a.EntireRow.Hidden = True Next End Sub Please assist, I would be very grateful as I am at a loss right now. -- Greg |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
hiding rows using macro
Sub hideifzeros()
Dim a as Range For Each a In Range(Cells(1,1),Cells(rows.count,1).End(xlup)) If a = 0 And a.Offset(0, 3) = 0 and _ not isempty(a) and Not isempty(a.offset(0,3)) _ Then a.EntireRow.Hidden = True Next End Sub would be a start. I can only imagine that these two other type rows are blank in columns A and D. to demo from the immediate window ActiveCell.ClearContents ? activeCell = 0 True when you compare an empty activecell to 0 it is coerced to zero so the comparison is number to number - thus it passes the test. The additions explicitly check if the cells are empty. -- Regards, Tom Ogilvy "Greg Stevens" wrote in message ... Tom, You are a savior, this works great but I have a few more questions and I would appreciate your comment. Between the rows filled with data (numbers) we have rows that are empty for spacing purpose (filler) and rows with wording (text). The macro is running correctly but it is collapsing the filler and text rows, in which we would like these rows to remain unhidden. Is there an addition code that we can put in? Thanks in advance. Regards, Greg "Tom Ogilvy" wrote: Sub hideifzeros() Dim a as Range For Each a In Range(Cells(1,1),Cells(rows.count,1).End(xlup)) If a = 0 And a.Offset(0, 3) = 0 Then a.EntireRow.Hidden = True Next End Sub column D is offset 3 from Column A. -- Regards, Tom Ogilvy "Greg Stevens" wrote in message ... Hello, I am looking for some assistance here with a code I am trying to write for an excel data. Here is the scenario: I have 10 columns (A:J) with about 1000 rows. Each of those cells within the 10 columns and 1000 rows contain numbers. We want to run a macro in which for columns A and D, when and only if they both equal "0"then we want to hide that specific row and continue onto the next row and keep on hiding if the columns A and D both equal "zero" until the entire sheet has been searched. So far here is what I have but the macro is just taking the first row and hiding regardless of values and stopping the Sub hideifzeros() For Each a In Selection If a = 0 And a.Offset(0, 4) = 0 Then a.EntireRow.Hidden = True Next End Sub Please assist, I would be very grateful as I am at a loss right now. -- Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro for hiding rows | New Users to Excel | |||
How to code Macro for hiding rows | Excel Worksheet Functions | |||
Hiding Rows in a VBA macro | Excel Discussion (Misc queries) | |||
Macro for hiding rows | Excel Discussion (Misc queries) | |||
Macro for hiding rows | Excel Discussion (Misc queries) |