excel.sheet.setprintArea
-
Hello Gurus... I want the exiting excel file to open it, format it and print it. opening and formating part I have ( mostly) done ... but to printing giving me problems.. when the file get print, there are lots of empty pages because of the last cell. let's say, the sheet contain only two cells that has some data. the first cell is A1 and the last cell is z130 and there are no data in the cells between first and last cells. So when I call PrintOut method it prints all the empty pages. I'm trying to set the print area for the excel sheet because I don't want to print empty pages. But how can i set the print area only to print that two cells??? I tried look every cells whether if it is empty or not, if empty don't do anything but if not empty, set the range and set the printarea... the following is my code..I know it is wrong but don't know how to fix it
Function SetPrintArea(sheet As Worksheet) As Worksheet Dim r As Range Set r = sheet.UsedRange Dim newRange As Range Dim i As Integer Dim j As Integer For i = 1 To r.Rows.Count For j = 1 To r.Columns.Count If Not Cells(i, j).Value = "" Then newRange = newRange(Cells(1, i), Cells(i, j)) End If Next j Next i newRange.Cells.Select sheet.PageSetup.PrintArea = Selection.Address Set SetPrintArea = sheet End FunctionThanks -
Hello Gurus... I want the exiting excel file to open it, format it and print it. opening and formating part I have ( mostly) done ... but to printing giving me problems.. when the file get print, there are lots of empty pages because of the last cell. let's say, the sheet contain only two cells that has some data. the first cell is A1 and the last cell is z130 and there are no data in the cells between first and last cells. So when I call PrintOut method it prints all the empty pages. I'm trying to set the print area for the excel sheet because I don't want to print empty pages. But how can i set the print area only to print that two cells??? I tried look every cells whether if it is empty or not, if empty don't do anything but if not empty, set the range and set the printarea... the following is my code..I know it is wrong but don't know how to fix it
Function SetPrintArea(sheet As Worksheet) As Worksheet Dim r As Range Set r = sheet.UsedRange Dim newRange As Range Dim i As Integer Dim j As Integer For i = 1 To r.Rows.Count For j = 1 To r.Columns.Count If Not Cells(i, j).Value = "" Then newRange = newRange(Cells(1, i), Cells(i, j)) End If Next j Next i newRange.Cells.Select sheet.PageSetup.PrintArea = Selection.Address Set SetPrintArea = sheet End FunctionThanksokay, I'm gonna ask the obvious question.... why are the only two cells in the sheet with the data so frikkin' far ????. That's gotta say something bout the design! the
Rangeobject also accepts parameters in the form of a string, like so :"A1:C5"The above parameter would include everything from cells A1 to cells C5 But if you wanna do composite ranges, then the following string:"A1:C5,D12:G7"would include everything from the first string as well as everything from cell D12 to G7 and nothing in between. I assume you could solve your problem by constructing the composite range string before hand, then pass it into theRangeobject Hope that helps Notorious SMC
The difference between the almost-right word & the right word is a really large matter - it's the difference between the lightning bug and the Lightning Mark Twain
Get your facts first, and then you can distort them as much as you please Mark Twain -
okay, I'm gonna ask the obvious question.... why are the only two cells in the sheet with the data so frikkin' far ????. That's gotta say something bout the design! the
Rangeobject also accepts parameters in the form of a string, like so :"A1:C5"The above parameter would include everything from cells A1 to cells C5 But if you wanna do composite ranges, then the following string:"A1:C5,D12:G7"would include everything from the first string as well as everything from cell D12 to G7 and nothing in between. I assume you could solve your problem by constructing the composite range string before hand, then pass it into theRangeobject Hope that helps Notorious SMC
The difference between the almost-right word & the right word is a really large matter - it's the difference between the lightning bug and the Lightning Mark Twain
Get your facts first, and then you can distort them as much as you please Mark TwainYou're damn right.. why do they do so?? Can't they just insert the information into the closer cells?? I, myself, want to know why they do that.. my job is to print that excel file..minimizing the number of pages that will print as possible. Thank you for answer SMC, So how can i get the position of the cell? Thank you
-
You're damn right.. why do they do so?? Can't they just insert the information into the closer cells?? I, myself, want to know why they do that.. my job is to print that excel file..minimizing the number of pages that will print as possible. Thank you for answer SMC, So how can i get the position of the cell? Thank you
you're looping method seem to work fine.... just don't assign the cells into the range yet. Convert into the string version first, then after evrything has finished looping, assign the string into the range Notorious SMC
The difference between the almost-right word & the right word is a really large matter - it's the difference between the lightning bug and the Lightning Mark Twain
Get your facts first, and then you can distort them as much as you please Mark Twain -
you're looping method seem to work fine.... just don't assign the cells into the range yet. Convert into the string version first, then after evrything has finished looping, assign the string into the range Notorious SMC
The difference between the almost-right word & the right word is a really large matter - it's the difference between the lightning bug and the Lightning Mark Twain
Get your facts first, and then you can distort them as much as you please Mark Twain -
thanks. I did the samething.. Is there anyway that I can find out whether the sheet(s) has any protection?? what if I don't know the password?? Thanks
Win wrote: Is there anyway that I can find out whether the sheet(s) has any protection?? what if I don't know the password?? err..... sorry, don't know much in that area. Notorious SMC
The difference between the almost-right word & the right word is a really large matter - it's the difference between the lightning bug and the Lightning Mark Twain
Get your facts first, and then you can distort them as much as you please Mark Twain