Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide rows based on criteria
I am in the process of doing end of year financial reporting analysis
and I want to be able to run a macro that hides empty rows based on a specific criteria. Column C lists the accounts codes, column G is the YTD Actuals and column H is the YTD Budget. What I want to do, is..... If there is a value in a row in Column C and if the value of the cell in both column G & H is 0 the hide the row. If anyone can point me in the right direction to research this further would be sincerely appreciated. PS: I am using MS Excel 2003 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide rows based on criteria
try to select yr data and use the macro:
Sub hideeeee() On Error Resume Next For Each cell In Selection If IsNumeric(cell) And cell.Offset(0, 4) = 0 And cell.Offset(0, 5) = 0 Then cell.Rows.EntireRow.Hidden = True End If Next cell End Sub HIH |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide rows based on criteria
Try this
Sub stantial() Dim myrange, hiderange As Range Lastrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row Set myrange = Range("C1:C" & Lastrow) For Each c In myrange If c.Value < "" And c.Offset(, 4) = 0 And c.Offset(, 5) = 0 _ And c.Offset(, 4) < "" And c.Offset(, 5) < "" Then If hiderange Is Nothing Then Set hiderange = c.EntireRow Else Set hiderange = Union(hiderange, c.EntireRow) End If End If Next If Not hiderange Is Nothing Then hiderange.EntireRow.Hidden = True End If End Sub Mike "Santa-D" wrote: I am in the process of doing end of year financial reporting analysis and I want to be able to run a macro that hides empty rows based on a specific criteria. Column C lists the accounts codes, column G is the YTD Actuals and column H is the YTD Budget. What I want to do, is..... If there is a value in a row in Column C and if the value of the cell in both column G & H is 0 the hide the row. If anyone can point me in the right direction to research this further would be sincerely appreciated. PS: I am using MS Excel 2003 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide rows based on criteria
Mike is right
an empty cell will been as '0', so I have to adjust my macro Sub hideeeee() On Error Resume Next For Each cell In Selection If IsNumeric(cell) And cell.Offset(0, 4) = 0 And cell.Offset(0, 5) = 0 _ And Len(cell.Offset(0, 4)) 0 And Len(cell.Offset(0, 5) 0 Then cell.Rows.EntireRow.Hidden = True End If Next cell End Sub sorry |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide rows based on criteria
Thanks Jarek & Mike,
I was on the verge of using the xlUp but I've got a series of blank rows in between each of the categories. I ended up using a modified version of the with selection... "E" for expense & "R" for revenue. If the value was "" then it would skip and if it was "H" for Heading then it would be fine. Sub hideeeee() On Error Resume Next For Each cell In Selection If cell.Offset(0, -2) = "R" Or cell.Offset(0, -2) = "E" Then If cell.Offset(0, 4) = 0 And cell.Offset(0, 5) = 0 _ And Len(cell.Offset(0, 4)) 0 And Len(cell.Offset(0, 5)) 0 Then cell.Rows.EntireRow.Hidden = True End If End If Next cell End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to hide rows based on criteria
On Jul 1, 4:00*pm, Santa-D wrote:
Thanks Jarek & Mike, I was on the verge of using the xlUp but I've got a series of blank rows in between each of the categories. I ended up using a modified version of the with selection... "E" for expense & "R" for revenue. *If the value was "" then it would skip and if it was "H" for Heading then it would be fine. Sub hideeeee() On Error Resume Next For Each cell In Selection If cell.Offset(0, -2) = "R" Or cell.Offset(0, -2) = "E" Then * * If cell.Offset(0, 4) = 0 And cell.Offset(0, 5) = 0 _ * * * And Len(cell.Offset(0, 4)) 0 And Len(cell.Offset(0, 5)) 0 Then cell.Rows.EntireRow.Hidden = True End If End If Next cell End Sub The only downside is that it didn't keep the highlight second row conditional formatting when hiding all those rows....... :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to show/hide rows based on a range of dates | Excel Programming | |||
Macro to Hide rows based on value of column F | Excel Discussion (Misc queries) | |||
Macro to hide and unhide based on criteria | Excel Discussion (Misc queries) | |||
Macro to hide rows based on a zero value in a particular cell | Excel Programming | |||
macro to hide rows based on formula? | Excel Programming |