Hiding Blank lines in named range

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
 
C

Cecilkumara Fernando

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
 
C

Carl Brehm

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
 
C

Cecilkumara Fernando

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Slow Response time 4
sum (column 2 of namedrange) 4
Error 1004 help 4
Sumif Help needed 3
Range("Weekending").Cells.Columns.Count 4
.cells help 3
Changing color of cells 5
Function to convert string 13

Top