Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide empty rows
Hello,
I am trying to hide empty rows in a named range (A5:BU1111) where all "empty" rows however do have a formula in several columns (where the value is ""). The only way I have managed is to create a dummy column (which I have named "ROW_SHRINK_AREA", which creates some values which indicate that the row in question is not empty. Then I use the macro: Sub MakeEmptyRowsGoAway() Application.Goto Reference:="ROW_SHRINK_COLUMN" For Each R In Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Range("ROW_SHRINK_COLUMN")).Cells varValue = R.Value If IsNumeric(varValue) Then If varValue = 0 Then R.EntireRow.Hidden = True End If End If Next R End Sub Would someone please advise me a faster method? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide empty rows
Give this macro a try (without the dummy column) and see if it does what you
want... Sub HideEmptyRows() Dim R As Range For Each R In Range("A5:BU1111").Rows If WorksheetFunction.CountA(R) = R.Count Then R.Hidden = True Next End Sub -- Rick (MVP - Excel) "marjattanb" wrote in message ... Hello, I am trying to hide empty rows in a named range (A5:BU1111) where all "empty" rows however do have a formula in several columns (where the value is ""). The only way I have managed is to create a dummy column (which I have named "ROW_SHRINK_AREA", which creates some values which indicate that the row in question is not empty. Then I use the macro: Sub MakeEmptyRowsGoAway() Application.Goto Reference:="ROW_SHRINK_COLUMN" For Each R In Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Range("ROW_SHRINK_COLUMN")).Cells varValue = R.Value If IsNumeric(varValue) Then If varValue = 0 Then R.EntireRow.Hidden = True End If End If Next R End Sub Would someone please advise me a faster method? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide empty rows
I don't get that macro to work if there are cells containing formulas that
evaluate to the empty string. -- Rick (MVP - Excel) "Don Guillett" wrote in message ... You may desire to use a macro that looks at the number of hits Sub hideemptyrows() For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row If Application.CountA(Rows(i)) < 1 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "marjattanb" wrote in message ... Hello, I am trying to hide empty rows in a named range (A5:BU1111) where all "empty" rows however do have a formula in several columns (where the value is ""). The only way I have managed is to create a dummy column (which I have named "ROW_SHRINK_AREA", which creates some values which indicate that the row in question is not empty. Then I use the macro: Sub MakeEmptyRowsGoAway() Application.Goto Reference:="ROW_SHRINK_COLUMN" For Each R In Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Range("ROW_SHRINK_COLUMN")).Cells varValue = R.Value If IsNumeric(varValue) Then If varValue = 0 Then R.EntireRow.Hidden = True End If End If Next R End Sub Would someone please advise me a faster method? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide empty rows
Ignore my code... it does not do what you want.
-- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... I don't get that macro to work if there are cells containing formulas that evaluate to the empty string. -- Rick (MVP - Excel) "Don Guillett" wrote in message ... You may desire to use a macro that looks at the number of hits Sub hideemptyrows() For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row If Application.CountA(Rows(i)) < 1 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "marjattanb" wrote in message ... Hello, I am trying to hide empty rows in a named range (A5:BU1111) where all "empty" rows however do have a formula in several columns (where the value is ""). The only way I have managed is to create a dummy column (which I have named "ROW_SHRINK_AREA", which creates some values which indicate that the row in question is not empty. Then I use the macro: Sub MakeEmptyRowsGoAway() Application.Goto Reference:="ROW_SHRINK_COLUMN" For Each R In Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Range("ROW_SHRINK_COLUMN")).Cells varValue = R.Value If IsNumeric(varValue) Then If varValue = 0 Then R.EntireRow.Hidden = True End If End If Next R End Sub Would someone please advise me a faster method? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide empty rows
Hi
As you don't tell what the value in ROW_SHRINK_AREA is, I assume the formula return a blank if the entire row is empty. If your formula return 0 for an empty row, edit your formula like this: =If("Your formula" =0,"", "Your formula") Then you can use either of the suggestions below. Sub MakeEmptyRowsGoAway() Dim TargetCol As Range Set TargetCol = Range("ROW_SHRINK_COLUMN").SpecialCells(xlCellType Blanks) TargetCol.EntireRow.Hidden = True 'OR With Range("ROW_SHRINK_COLUMN") .AutoFilter Field:=1, Criteria1:="=" Set TargetCol = .SpecialCells(xlCellTypeVisible) .AutoFilter End With TargetCol.EntireRow.Hidden = True End Sub Regards, Per "marjattanb" skrev i meddelelsen ... Hello, I am trying to hide empty rows in a named range (A5:BU1111) where all "empty" rows however do have a formula in several columns (where the value is ""). The only way I have managed is to create a dummy column (which I have named "ROW_SHRINK_AREA", which creates some values which indicate that the row in question is not empty. Then I use the macro: Sub MakeEmptyRowsGoAway() Application.Goto Reference:="ROW_SHRINK_COLUMN" For Each R In Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Range("ROW_SHRINK_COLUMN")).Cells varValue = R.Value If IsNumeric(varValue) Then If varValue = 0 Then R.EntireRow.Hidden = True End If End If Next R End Sub Would someone please advise me a faster method? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide empty rows
Okay, I am pretty sure this macro does what you want...
Sub HideEmptyRows() Dim R As Range Dim SearchRange As Range Dim LastRow As Long Dim ColCellCount As Long Set SearchRange = Range("A5:BU1111") ColCellCount = SearchRange.Columns.Count LastRow = SearchRange.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious).Row For Each R In SearchRange.Resize(LastRow - SearchRange(1).Row).Rows If WorksheetFunction.CountBlank(R) = ColCellCount Then R.Hidden = True Next End Sub -- Rick (MVP - Excel) "marjattanb" wrote in message ... Hello, I am trying to hide empty rows in a named range (A5:BU1111) where all "empty" rows however do have a formula in several columns (where the value is ""). The only way I have managed is to create a dummy column (which I have named "ROW_SHRINK_AREA", which creates some values which indicate that the row in question is not empty. Then I use the macro: Sub MakeEmptyRowsGoAway() Application.Goto Reference:="ROW_SHRINK_COLUMN" For Each R In Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Range("ROW_SHRINK_COLUMN")).Cells varValue = R.Value If IsNumeric(varValue) Then If varValue = 0 Then R.EntireRow.Hidden = True End If End If Next R End Sub Would someone please advise me a faster method? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide empty rows
Try this
Sub hideemptyrows() lr = Cells.Find("*", Cells(Rows.Count, Columns.Count), , , xlByRows, xlPrevious).Row For i = 1 To lr lc = Cells(i, Columns.Count).End(xlToLeft).Column For j = 1 To lc If Application.CountA(Rows(i)) < 1 Or _ Cells(i, j).HasFormula And Cells(i, j) = "" Then Rows(i).Hidden = True End If Next j Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Rick Rothstein" wrote in message ... I don't get that macro to work if there are cells containing formulas that evaluate to the empty string. -- Rick (MVP - Excel) "Don Guillett" wrote in message ... You may desire to use a macro that looks at the number of hits Sub hideemptyrows() For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row If Application.CountA(Rows(i)) < 1 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "marjattanb" wrote in message ... Hello, I am trying to hide empty rows in a named range (A5:BU1111) where all "empty" rows however do have a formula in several columns (where the value is ""). The only way I have managed is to create a dummy column (which I have named "ROW_SHRINK_AREA", which creates some values which indicate that the row in question is not empty. Then I use the macro: Sub MakeEmptyRowsGoAway() Application.Goto Reference:="ROW_SHRINK_COLUMN" For Each R In Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Range("ROW_SHRINK_COLUMN")).Cells varValue = R.Value If IsNumeric(varValue) Then If varValue = 0 Then R.EntireRow.Hidden = True End If End If Next R End Sub Would someone please advise me a faster method? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide empty rows
Thanks - but I still seem to be stuck. I made it to look like this:
Sub hideemptyrows() Application.Goto Reference:="ROW_SHRINK_AREA" For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row If Application.CountA(Rows(i)) = 0 Then Rows(i).Hidden = True Next i End Sub - thinking that it would look for zeroes within my dummy column and consequently hide those rows entirely. Still nothing happens - in addition of the area being selected. Anything I do wrong? - sure there is! :) Thanking again Sub hideemptyrows() Application.Goto Reference:="ROW_SHRINK_AREA" For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row If Application.CountA(Rows(i)) = 0 Then Rows(i).Hidden = True Next i End Sub "Don Guillett" wrote: You may desire to use a macro that looks at the number of hits Sub hideemptyrows() For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row If Application.CountA(Rows(i)) < 1 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "marjattanb" wrote in message ... Hello, I am trying to hide empty rows in a named range (A5:BU1111) where all "empty" rows however do have a formula in several columns (where the value is ""). The only way I have managed is to create a dummy column (which I have named "ROW_SHRINK_AREA", which creates some values which indicate that the row in question is not empty. Then I use the macro: Sub MakeEmptyRowsGoAway() Application.Goto Reference:="ROW_SHRINK_COLUMN" For Each R In Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Range("ROW_SHRINK_COLUMN")).Cells varValue = R.Value If IsNumeric(varValue) Then If varValue = 0 Then R.EntireRow.Hidden = True End If End If Next R End Sub Would someone please advise me a faster method? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide empty rows
You need to pick up i from the first row in your ROW_SHRINK_AREA. Try
For i = ActiveCell.Row To... instead of For i = 1 To ... -- Steve "marjattanb" wrote in message ... Thanks - but I still seem to be stuck. I made it to look like this: Sub hideemptyrows() Application.Goto Reference:="ROW_SHRINK_AREA" For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row If Application.CountA(Rows(i)) = 0 Then Rows(i).Hidden = True Next i End Sub - thinking that it would look for zeroes within my dummy column and consequently hide those rows entirely. Still nothing happens - in addition of the area being selected. Anything I do wrong? - sure there is! :) Thanking again Sub hideemptyrows() Application.Goto Reference:="ROW_SHRINK_AREA" For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row If Application.CountA(Rows(i)) = 0 Then Rows(i).Hidden = True Next i End Sub "Don Guillett" wrote: You may desire to use a macro that looks at the number of hits Sub hideemptyrows() For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row If Application.CountA(Rows(i)) < 1 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "marjattanb" wrote in message ... Hello, I am trying to hide empty rows in a named range (A5:BU1111) where all "empty" rows however do have a formula in several columns (where the value is ""). The only way I have managed is to create a dummy column (which I have named "ROW_SHRINK_AREA", which creates some values which indicate that the row in question is not empty. Then I use the macro: Sub MakeEmptyRowsGoAway() Application.Goto Reference:="ROW_SHRINK_COLUMN" For Each R In Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Range("ROW_SHRINK_COLUMN")).Cells varValue = R.Value If IsNumeric(varValue) Then If varValue = 0 Then R.EntireRow.Hidden = True End If End If Next R End Sub Would someone please advise me a faster method? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide empty rows
Hi,
thanks again (do u ever sleep??) Now it gets stuck with SYNTAX ERROR with this: lr = Cells.Find("*", Cells(Rows.Count, Columns.Count), , , xlByRows, xlPrevious).Row And I am stuck again.. "Don Guillett" wrote: Try this Sub hideemptyrows() lr = Cells.Find("*", Cells(Rows.Count, Columns.Count), , , xlByRows, xlPrevious).Row For i = 1 To lr lc = Cells(i, Columns.Count).End(xlToLeft).Column For j = 1 To lc If Application.CountA(Rows(i)) < 1 Or _ Cells(i, j).HasFormula And Cells(i, j) = "" Then Rows(i).Hidden = True End If Next j Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Rick Rothstein" wrote in message ... I don't get that macro to work if there are cells containing formulas that evaluate to the empty string. -- Rick (MVP - Excel) "Don Guillett" wrote in message ... You may desire to use a macro that looks at the number of hits Sub hideemptyrows() For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row If Application.CountA(Rows(i)) < 1 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "marjattanb" wrote in message ... Hello, I am trying to hide empty rows in a named range (A5:BU1111) where all "empty" rows however do have a formula in several columns (where the value is ""). The only way I have managed is to create a dummy column (which I have named "ROW_SHRINK_AREA", which creates some values which indicate that the row in question is not empty. Then I use the macro: Sub MakeEmptyRowsGoAway() Application.Goto Reference:="ROW_SHRINK_COLUMN" For Each R In Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Range("ROW_SHRINK_COLUMN")).Cells varValue = R.Value If IsNumeric(varValue) Then If varValue = 0 Then R.EntireRow.Hidden = True End If End If Next R End Sub Would someone please advise me a faster method? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide empty rows
Try this. You can remove the code between blank rows and (zero + blank)
rows..to suit your requirement Sub Hideemptyrows() For lngRow = 1 To Cells(Rows.Count, 1).End(xlUp).Row 'Hide rows with zeros and blanks If WorksheetFunction.CountIf(Rows(lngRow), 0) + _ WorksheetFunction.CountBlank(Rows(lngRow)) = _ Columns.Count Then Rows(lngRow).Hidden = True 'Hide rows with blanks 'If WorksheetFunction.CountBlank(Rows(1)) = Columns.Count _ 'Then Rows(lngRow).Hidden = True Next End Sub -- If this post helps click Yes --------------- Jacob Skaria "marjattanb" wrote: Hello, I am trying to hide empty rows in a named range (A5:BU1111) where all "empty" rows however do have a formula in several columns (where the value is ""). The only way I have managed is to create a dummy column (which I have named "ROW_SHRINK_AREA", which creates some values which indicate that the row in question is not empty. Then I use the macro: Sub MakeEmptyRowsGoAway() Application.Goto Reference:="ROW_SHRINK_COLUMN" For Each R In Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Range("ROW_SHRINK_COLUMN")).Cells varValue = R.Value If IsNumeric(varValue) Then If varValue = 0 Then R.EntireRow.Hidden = True End If End If Next R End Sub Would someone please advise me a faster method? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide empty rows
If your "ROW_SHRINK_AREA" and Range("A5:BU1111") have no intersections,
then try this one. Sub testgoawayEmpty() Dim i As Long Application.ScreenUpdating = False For i = 5 To 1111 If Application.CountA(Range(Cells(i, "A"), Cells(i, "BU"))) = 0 Then Rows(i).Hidden = True End If Application.StatusBar = "Now is in Rows(" & i & " )" Next End Sub Keiji marjattanb wrote: Thanks - but I still seem to be stuck. I made it to look like this: Sub hideemptyrows() Application.Goto Reference:="ROW_SHRINK_AREA" For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row If Application.CountA(Rows(i)) = 0 Then Rows(i).Hidden = True Next i End Sub - thinking that it would look for zeroes within my dummy column and consequently hide those rows entirely. Still nothing happens - in addition of the area being selected. Anything I do wrong? - sure there is! :) Thanking again Sub hideemptyrows() Application.Goto Reference:="ROW_SHRINK_AREA" For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row If Application.CountA(Rows(i)) = 0 Then Rows(i).Hidden = True Next i End Sub "Don Guillett" wrote: You may desire to use a macro that looks at the number of hits Sub hideemptyrows() For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row If Application.CountA(Rows(i)) < 1 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "marjattanb" wrote in message ... Hello, I am trying to hide empty rows in a named range (A5:BU1111) where all "empty" rows however do have a formula in several columns (where the value is ""). The only way I have managed is to create a dummy column (which I have named "ROW_SHRINK_AREA", which creates some values which indicate that the row in question is not empty. Then I use the macro: Sub MakeEmptyRowsGoAway() Application.Goto Reference:="ROW_SHRINK_COLUMN" For Each R In Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Range("ROW_SHRINK_COLUMN")).Cells varValue = R.Value If IsNumeric(varValue) Then If varValue = 0 Then R.EntireRow.Hidden = True End If End If Next R End Sub Would someone please advise me a faster method? |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide empty rows
Hmm! I see I put my "ignore this code" message on the wrong sub-thread. This
was the code you were supposed to ignore because it didn't work; HOWEVER, do try my later posted code as that does work. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Give this macro a try (without the dummy column) and see if it does what you want... Sub HideEmptyRows() Dim R As Range For Each R In Range("A5:BU1111").Rows If WorksheetFunction.CountA(R) = R.Count Then R.Hidden = True Next End Sub -- Rick (MVP - Excel) "marjattanb" wrote in message ... Hello, I am trying to hide empty rows in a named range (A5:BU1111) where all "empty" rows however do have a formula in several columns (where the value is ""). The only way I have managed is to create a dummy column (which I have named "ROW_SHRINK_AREA", which creates some values which indicate that the row in question is not empty. Then I use the macro: Sub MakeEmptyRowsGoAway() Application.Goto Reference:="ROW_SHRINK_COLUMN" For Each R In Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Range("ROW_SHRINK_COLUMN")).Cells varValue = R.Value If IsNumeric(varValue) Then If varValue = 0 Then R.EntireRow.Hidden = True End If End If Next R End Sub Would someone please advise me a faster method? |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide empty rows
Did you notice a different color??? Below is ONE line. So either use the
delete key to bring up the second part or put in a line continuation character which is a space and underscore. xlByRows _ lr = Cells.Find("*", Cells(Rows.Count, Columns.Count), , , xlByRows, xlPrevious).Row -- Don Guillett Microsoft MVP Excel SalesAid Software "marjattanb" wrote in message ... Hi, thanks again (do u ever sleep??) Now it gets stuck with SYNTAX ERROR with this: lr = Cells.Find("*", Cells(Rows.Count, Columns.Count), , , xlByRows, xlPrevious).Row And I am stuck again.. "Don Guillett" wrote: Try this Sub hideemptyrows() lr = Cells.Find("*", Cells(Rows.Count, Columns.Count), , , xlByRows, xlPrevious).Row For i = 1 To lr lc = Cells(i, Columns.Count).End(xlToLeft).Column For j = 1 To lc If Application.CountA(Rows(i)) < 1 Or _ Cells(i, j).HasFormula And Cells(i, j) = "" Then Rows(i).Hidden = True End If Next j Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Rick Rothstein" wrote in message ... I don't get that macro to work if there are cells containing formulas that evaluate to the empty string. -- Rick (MVP - Excel) "Don Guillett" wrote in message ... You may desire to use a macro that looks at the number of hits Sub hideemptyrows() For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row If Application.CountA(Rows(i)) < 1 Then Rows(i).Hidden = True Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "marjattanb" wrote in message ... Hello, I am trying to hide empty rows in a named range (A5:BU1111) where all "empty" rows however do have a formula in several columns (where the value is ""). The only way I have managed is to create a dummy column (which I have named "ROW_SHRINK_AREA", which creates some values which indicate that the row in question is not empty. Then I use the macro: Sub MakeEmptyRowsGoAway() Application.Goto Reference:="ROW_SHRINK_COLUMN" For Each R In Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Range("ROW_SHRINK_COLUMN")).Cells varValue = R.Value If IsNumeric(varValue) Then If varValue = 0 Then R.EntireRow.Hidden = True End If End If Next R End Sub Would someone please advise me a faster method? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help on Macro to hide empty rows | Excel Discussion (Misc queries) | |||
Is there a way to automatically hide empty rows? | Excel Worksheet Functions | |||
Macro to hide rows with a certain cell empty then set print area and print | Excel Programming | |||
URGENT!! Use macro button to hide empty columns and rows...HELP!!!! | Excel Programming | |||
Macro to hide rows with empty cells | Excel Worksheet Functions |