Hiding Blank lines in named range
 

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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.504 / Virus Database: 302 - Release Date: 07/24/2003

Hiding Blank lines in named range
 

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
"Carl Brehm" wrote in message ...
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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.504 / Virus Database: 302 - Release Date: 07/24/2003

Hiding Blank lines in named range
 

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
"Cecilkumara Fernando" wrote in message ...
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
"Carl Brehm" wrote in message ...
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


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.504 / Virus Database: 302 - Release Date: 07/24/2003

Hiding Blank lines in named range
 

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
"Carl Brehm" wrote in message ...
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.