| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Ignore blank cells in a named range when using dependent data validation | Excel Discussion (Misc queries) | |||
| Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
| inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
| Dynamic Named Range with blank cells | Excel Discussion (Misc queries) | |||
| Blank cells in named range- how to ignore them when making my graph? Help plz! | Excel Discussion (Misc queries) | |||
Linear Mode
