Hiding Blank lines in named range

  • Thread starter Thread starter Carl Brehm
  • Start date Start date
C

Carl Brehm

Have a named range onhand
The first column in the range will always have a string value.
The rest of the columns may or may not have a value.
Need to check each row in range to see if they are empty or contain a 0 then I would like to hide that row so it would not print.

ie... hide the first 2 rows in example
CUTE! CUTE! CUTE! Nobody ever says anything about my "BRAINS"!
Daddy got me this T-Shirt Because He Loves Me.
Daddy Knows a lot, but Grandpa Knows Everything. 2 2 2 2 1 1 3



Sub Print_Onhand_Sheet()

Application.ScreenUpdating = False

With ActiveSheet.PageSetup
.PrintArea = ActiveWorkbook.Names.Item("onhand")
.PrintTitleColumns = ActiveWorkbook.Names.Item("onhandheading")
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 80
End With
ActiveWindow.SelectedSheets.PrintOut
Application.ScreenUpdating = True
End Sub

--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN & Red Lories, Quakers
Mitred Conures, TAG's,DYH, Bourkes,
Cages, Toys, Toy parts Wholesale/Retail
Feed & Supplies
 
Carl,
Try this
Sub Print_Onhand_Sheet()
For i = Range("onhand").Cells(1, 1).Row To _
Range("onhand").Cells(1, 1).Row + _
Range("onhand").Rows.Count - 1
If WorksheetFunction. _
Sum(Range(Cells(i, 2), Cells(i, 10))) = 0 Then
Range("A" & i).EntireRow.Hidden = True
End If
Next i
Application.ScreenUpdating = False

With ActiveSheet.PageSetup
.PrintArea = ActiveWorkbook.Names.Item("onhand")
.PrintTitleColumns = ActiveWorkbook.Names.Item("onhandheading")
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 80
End With
ActiveWindow.SelectedSheets.PrintOut
Application.ScreenUpdating = True
Range("onhand").EntireRow.Hidden = False

End Sub
Have a named range onhand
The first column in the range will always have a string value.
The rest of the columns may or may not have a value.
Need to check each row in range to see if they are empty or contain a 0 then I would like to hide that row so it would not print.

ie... hide the first 2 rows in example
CUTE! CUTE! CUTE! Nobody ever says anything about my "BRAINS"!
Daddy got me this T-Shirt Because He Loves Me.
Daddy Knows a lot, but Grandpa Knows Everything. 2 2 2 2 1 1 3



Sub Print_Onhand_Sheet()

Application.ScreenUpdating = False

With ActiveSheet.PageSetup
.PrintArea = ActiveWorkbook.Names.Item("onhand")
.PrintTitleColumns = ActiveWorkbook.Names.Item("onhandheading")
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 80
End With
ActiveWindow.SelectedSheets.PrintOut
Application.ScreenUpdating = True
End Sub

--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN & Red Lories, Quakers
Mitred Conures, TAG's,DYH, Bourkes,
Cages, Toys, Toy parts Wholesale/Retail
Feed & Supplies
 
Two problems.
1. It is hiding row 1 which is not in the range ("Onhand")
2. It assumes that the columns end at 10 which is not the case. The number of columns in Range("onhand") need to be counted, not assumed.

--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN & Red Lories, Quakers
Mitred Conures, TAG's,DYH, Bourkes,
Cages, Toys, Toy parts Wholesale/Retail
Feed & Supplies
Carl,
Try this
Sub Print_Onhand_Sheet()
For i = Range("onhand").Cells(1, 1).Row To _
Range("onhand").Cells(1, 1).Row + _
Range("onhand").Rows.Count - 1
If WorksheetFunction. _
Sum(Range(Cells(i, 2), Cells(i, 10))) = 0 Then
Range("A" & i).EntireRow.Hidden = True
End If
Next i
Application.ScreenUpdating = False

With ActiveSheet.PageSetup
.PrintArea = ActiveWorkbook.Names.Item("onhand")
.PrintTitleColumns = ActiveWorkbook.Names.Item("onhandheading")
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 80
End With
ActiveWindow.SelectedSheets.PrintOut
Application.ScreenUpdating = True
Range("onhand").EntireRow.Hidden = False

End Sub
Have a named range onhand
The first column in the range will always have a string value.
The rest of the columns may or may not have a value.
Need to check each row in range to see if they are empty or contain a 0 then I would like to hide that row so it would not print.

ie... hide the first 2 rows in example
CUTE! CUTE! CUTE! Nobody ever says anything about my "BRAINS"!
Daddy got me this T-Shirt Because He Loves Me.
Daddy Knows a lot, but Grandpa Knows Everything. 2 2 2 2 1 1 3



Sub Print_Onhand_Sheet()

Application.ScreenUpdating = False

With ActiveSheet.PageSetup
.PrintArea = ActiveWorkbook.Names.Item("onhand")
.PrintTitleColumns = ActiveWorkbook.Names.Item("onhandheading")
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 80
End With
ActiveWindow.SelectedSheets.PrintOut
Application.ScreenUpdating = True
End Sub

--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN & Red Lories, Quakers
Mitred Conures, TAG's,DYH, Bourkes,
Cages, Toys, Toy parts Wholesale/Retail
Feed & Supplies
 
Carl Brehm,
1. It is hiding row 1 which is not in the range ("Onhand")
It didn't when I tested, If the row1 in the range ("Onhand") is the header row
then start the for-next loop at the next line
For i = Range("onhand").Cells(1, 1).Row + 1 To _
Range("onhand").Cells(1, 1).Row + _
Range("onhand").Rows.Count - 1

2. It assumes that the columns end at 10 which is not the case. The number of columns in Range("onhand") need to be counted, not assumed.
change if condition to
If WorksheetFunction. _
Sum(Range(Cells(i, 2), _
Cells(i, Range("onhand").Columns.Count))) = 0 Then

Cecil
Two problems.
1. It is hiding row 1 which is not in the range ("Onhand")
2. It assumes that the columns end at 10 which is not the case. The number of columns in Range("onhand") need to be counted, not assumed.
 
Back
Top