Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bland Rows
My spreadsheet has 250 rows and A thru AG in columns, all cells are bordered.
All of the columns are used if a row is used. My sheet has 15% more rows than our highest use this year so that I am sure our office does not run short of rows (my workbook has 12 of these sheets because we track the info monthly and forward the sheets as filled out to other personel). My question is how do I print the sheets (only one month is printed at a time) to print only the rows that has been filled out, all of the sheet rows are continuous, we have no unused rows except for the ones left at the bottom that are unused. when ever I print the sheets it always prints a few extra pages from the bottom that are just the gridding. I don't want to have to delete all of the unused row before I print the sheet. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bland Rows
Sub rhide()
Dim nLastRow As Long With ActiveSheet.UsedRange nLastRow = .Rows.Count + .Row - 1 End With For i = 1 To nLastRow If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).EntireRow.Hidden = True End If Next Set r = Range(Cells(nLastRow + 1, "A"), Cells(65536, "A")) r.EntireRow.Hidden = True End Sub This macro will hide any empty rows within your data. It will also hide any blank rows outside of your data. Just run it prior to printing. -- Gary's Student "tankerman" wrote: My spreadsheet has 250 rows and A thru AG in columns, all cells are bordered. All of the columns are used if a row is used. My sheet has 15% more rows than our highest use this year so that I am sure our office does not run short of rows (my workbook has 12 of these sheets because we track the info monthly and forward the sheets as filled out to other personel). My question is how do I print the sheets (only one month is printed at a time) to print only the rows that has been filled out, all of the sheet rows are continuous, we have no unused rows except for the ones left at the bottom that are unused. when ever I print the sheets it always prints a few extra pages from the bottom that are just the gridding. I don't want to have to delete all of the unused row before I print the sheet. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bland Rows
You can create a dynamic range named Print_Area, as described he
http://www.contextures.com/xlNames01.html In the Offset formula, you can count the cells that contain text. For example, if column A will always have an entry if the row is used: =OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A$1:$A$250 ,"<"),33) tankerman wrote: My spreadsheet has 250 rows and A thru AG in columns, all cells are bordered. All of the columns are used if a row is used. My sheet has 15% more rows than our highest use this year so that I am sure our office does not run short of rows (my workbook has 12 of these sheets because we track the info monthly and forward the sheets as filled out to other personel). My question is how do I print the sheets (only one month is printed at a time) to print only the rows that has been filled out, all of the sheet rows are continuous, we have no unused rows except for the ones left at the bottom that are unused. when ever I print the sheets it always prints a few extra pages from the bottom that are just the gridding. I don't want to have to delete all of the unused row before I print the sheet. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bland Rows
Just to add:
You just need to be aware that if you do File=Page Setup (no other action necessary), it removes your formula for that page and replaces it with a hard coded range. so avoid doing page setup. -- Regards, Tom Ogilvy "Debra Dalgleish" wrote in message ... You can create a dynamic range named Print_Area, as described he http://www.contextures.com/xlNames01.html In the Offset formula, you can count the cells that contain text. For example, if column A will always have an entry if the row is used: =OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A$1:$A$250 ,"<"),33) tankerman wrote: My spreadsheet has 250 rows and A thru AG in columns, all cells are bordered. All of the columns are used if a row is used. My sheet has 15% more rows than our highest use this year so that I am sure our office does not run short of rows (my workbook has 12 of these sheets because we track the info monthly and forward the sheets as filled out to other personel). My question is how do I print the sheets (only one month is printed at a time) to print only the rows that has been filled out, all of the sheet rows are continuous, we have no unused rows except for the ones left at the bottom that are unused. when ever I print the sheets it always prints a few extra pages from the bottom that are just the gridding. I don't want to have to delete all of the unused row before I print the sheet. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bland Rows
And you may want to make it a sheet level name:
Insert|Name|Define Names in workbook box: 'Sheet1'!Print_Area Debra Dalgleish wrote: You can create a dynamic range named Print_Area, as described he http://www.contextures.com/xlNames01.html In the Offset formula, you can count the cells that contain text. For example, if column A will always have an entry if the row is used: =OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A$1:$A$250 ,"<"),33) tankerman wrote: My spreadsheet has 250 rows and A thru AG in columns, all cells are bordered. All of the columns are used if a row is used. My sheet has 15% more rows than our highest use this year so that I am sure our office does not run short of rows (my workbook has 12 of these sheets because we track the info monthly and forward the sheets as filled out to other personel). My question is how do I print the sheets (only one month is printed at a time) to print only the rows that has been filled out, all of the sheet rows are continuous, we have no unused rows except for the ones left at the bottom that are unused. when ever I print the sheets it always prints a few extra pages from the bottom that are just the gridding. I don't want to have to delete all of the unused row before I print the sheet. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bland Rows
I got a syntax error on this line of the code ..PageSetup.PrintArea =
..Rows("1:" & mr).Address "Don Guillett" wrote: Right click excel icon in upper left next to fileview codeinsert thissavetest Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet lr = .Cells(.Rows.Count, "a").End(xlUp).Row For r = lr To 1 Step -1 If Application.CountA(.Rows(r)) 1 Then mr = .Rows(r).Row: Exit For Next r ..PageSetup.PrintArea = .Rows("1:" & mr).Address 'or to limit columns '.PageSetup.PrintArea = .Range("a2:x" & mr).Address End With End Sub -- Don Guillett SalesAid Software "tankerman" wrote in message ... My spreadsheet has 250 rows and A thru AG in columns, all cells are bordered. All of the columns are used if a row is used. My sheet has 15% more rows than our highest use this year so that I am sure our office does not run short of rows (my workbook has 12 of these sheets because we track the info monthly and forward the sheets as filled out to other personel). My question is how do I print the sheets (only one month is printed at a time) to print only the rows that has been filled out, all of the sheet rows are continuous, we have no unused rows except for the ones left at the bottom that are unused. when ever I print the sheets it always prints a few extra pages from the bottom that are just the gridding. I don't want to have to delete all of the unused row before I print the sheet. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bland Rows
I have tried to keep whatever is doing it NOT put in the additional dot.
..Rows("1:" & mr).Address should be .Rows("1:" & mr).Address Don Guillett SalesAid Software "tankerman" wrote in message ... I got a syntax error on this line of the code ..PageSetup.PrintArea = .Rows("1:" & mr).Address "Don Guillett" wrote: Right click excel icon in upper left next to fileview codeinsert thissavetest Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet lr = .Cells(.Rows.Count, "a").End(xlUp).Row For r = lr To 1 Step -1 If Application.CountA(.Rows(r)) 1 Then mr = .Rows(r).Row: Exit For Next r ..PageSetup.PrintArea = .Rows("1:" & mr).Address 'or to limit columns '.PageSetup.PrintArea = .Range("a2:x" & mr).Address End With End Sub -- Don Guillett SalesAid Software "tankerman" wrote in message ... My spreadsheet has 250 rows and A thru AG in columns, all cells are bordered. All of the columns are used if a row is used. My sheet has 15% more rows than our highest use this year so that I am sure our office does not run short of rows (my workbook has 12 of these sheets because we track the info monthly and forward the sheets as filled out to other personel). My question is how do I print the sheets (only one month is printed at a time) to print only the rows that has been filled out, all of the sheet rows are continuous, we have no unused rows except for the ones left at the bottom that are unused. when ever I print the sheets it always prints a few extra pages from the bottom that are just the gridding. I don't want to have to delete all of the unused row before I print the sheet. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bland Rows
Sorry, I forgot to mention that a sheet level name is required.
And thanks Tom, I'd never noticed that using Page Setup removes the dynamic formula. Dave Peterson wrote: And you may want to make it a sheet level name: Insert|Name|Define Names in workbook box: 'Sheet1'!Print_Area Debra Dalgleish wrote: You can create a dynamic range named Print_Area, as described he http://www.contextures.com/xlNames01.html In the Offset formula, you can count the cells that contain text. For example, if column A will always have an entry if the row is used: =OFFSET(Sheet1!$A$1,0,0,COUNTIF(Sheet1!$A$1:$A$250 ,"<"),33) tankerman wrote: My spreadsheet has 250 rows and A thru AG in columns, all cells are bordered. All of the columns are used if a row is used. My sheet has 15% more rows than our highest use this year so that I am sure our office does not run short of rows (my workbook has 12 of these sheets because we track the info monthly and forward the sheets as filled out to other personel). My question is how do I print the sheets (only one month is printed at a time) to print only the rows that has been filled out, all of the sheet rows are continuous, we have no unused rows except for the ones left at the bottom that are unused. when ever I print the sheets it always prints a few extra pages from the bottom that are just the gridding. I don't want to have to delete all of the unused row before I print the sheet. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Bland Rows
Don,
I was having the same issues and used your code. It works great except it prints one extra blank page. Any suggestions. Is there something I need to change in the code to prevent this from happening. Thanks, Mickey "Don Guillett" wrote: Right click excel icon in upper left next to fileview codeinsert thissavetest Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveSheet lr = .Cells(.Rows.Count, "a").End(xlUp).Row For r = lr To 1 Step -1 If Application.CountA(.Rows(r)) 1 Then mr = .Rows(r).Row: Exit For Next r ..PageSetup.PrintArea = .Rows("1:" & mr).Address 'or to limit columns '.PageSetup.PrintArea = .Range("a2:x" & mr).Address End With End Sub -- Don Guillett SalesAid Software "tankerman" wrote in message ... My spreadsheet has 250 rows and A thru AG in columns, all cells are bordered. All of the columns are used if a row is used. My sheet has 15% more rows than our highest use this year so that I am sure our office does not run short of rows (my workbook has 12 of these sheets because we track the info monthly and forward the sheets as filled out to other personel). My question is how do I print the sheets (only one month is printed at a time) to print only the rows that has been filled out, all of the sheet rows are continuous, we have no unused rows except for the ones left at the bottom that are unused. when ever I print the sheets it always prints a few extra pages from the bottom that are just the gridding. I don't want to have to delete all of the unused row before I print the sheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|