Setting Print Area

Jmmac04

Board Regular
Joined
Nov 16, 2004
Messages
185
Hi All~
I tried searching the board, but could not find exactly what I need.
I have a spreadsheet in which data will be entered into. The number of rows will differ each time it is used. The people using this are not very experienced in excel, so I need to automate things as much as possible. When the user is done entering data, I need to have a sum of column F in the last cell under the last bit of data in F (which will always be a different cell) then to have the print area set from that point to A1.
Any ideas?
Thanks much! :rolleyes:
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

clipro

Board Regular
Joined
Jan 12, 2005
Messages
195
Hi

First let column A for the needs of this macro
In cell A2 put the formula
=IF(ISBLANK(B2);"";1)
and in each cell bellow
=IF(ISBLANK(B3);"";A2+1)
IN cell A1 put
=MAX(C9:C22)

after that you can use this macro

few notes the values after > are approximate and depends to you to determinate them

' Print Macro
' Macro recorded by CliPro
'
Sub Print()
If Sheets("Sheet1").Range("A1").Value > 168 Then
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=3, Copies:=1
ElseIf Sheets("Sheet1").Range("A1").Value > 72 Then
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=2, Copies:=1
Else
Sheets("Sheet1").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1
End If
End Sub

I am sure that there is a better way but that is my proposal
 

OdinsDream

Well-known Member
Joined
May 19, 2002
Messages
541
Here's the code to do what you need, just paste it into a new module, and make a button on your worksheet to run it.

Code:
Sub SumIt()
    
    With Cells(1, 6).End(xlDown)
        .Offset(1, 0).FormulaR1C1 = "=SUM(R[-" & .Row & "]C:R[-1]C)"
        ActiveSheet.PageSetup.PrintArea = "$A$1:" & .Offset(1, 0).Address
    End With
    
End Sub
 

Jmmac04

Board Regular
Joined
Nov 16, 2004
Messages
185
Thanks Clipro and OdinsDream!
OdinsDream:
I pasted it into the module and added it. It looks like it is doing something, but it does not do the formula to sum column F...
any ideas?
 

OdinsDream

Well-known Member
Joined
May 19, 2002
Messages
541

ADVERTISEMENT

Do you have data in column F, starting at F1? If your data starts lower, you'll need to adjust the line:

With Cells(1, 6).End(xlDown)

to reflect the position. 6 is column "F", and 1 is the row it starts looking on. If your stuff begins on F2, you'll need to have Cells(2, 6).

If your data is not continuous, though... that is you have some blank rows, you'll need to change it to:

With Cells(Rows.Count, 6).End(xlUp)
 

Jmmac04

Board Regular
Joined
Nov 16, 2004
Messages
185
Thanks again OdinsDream:
I see where the problem is. There is a formula in F that is based on data input in cell B. If there is no data in B then the cell has " " in it. The totals were several rows down past that formula. Maybe I am going about this wrong.
I want to have them enter their data in A & B and have the rest populate, then total then set the print area.
Gilhar2.xls
ABCDEF
1DateGrossWeightNetWeightTonsPricePerTonTotalPrice
212/10/2004565656539456269.728$1.95$525.97
3456-25744-12.872$1.95-$25.10
4659003970019.85$1.95$38.71
512/10/20044554-21646-10.823$1.95-$21.10
612/10/2004565484556286452814.323$1.95$5,487.93
7484-25716-12.858$1.95-$25.07
8485484459284229.642$1.95$447.80
94849-21351-10.6755$1.95-$20.82
10445454454451924522259.62$1.95$43,406.26
Sheet1



thanks
 

OdinsDream

Well-known Member
Joined
May 19, 2002
Messages
541
The second revision I posted should work just fine, even when your data has blanks in it... the one with End(xlUp) in it. Make that change and you should be good to go. If you prefer not having a button to run this, you'll need to put this code in some kind of event, like OnPrint (if such a thing exists) or OnSave.
 

Forum statistics

Threads
1,147,620
Messages
5,742,177
Members
423,708
Latest member
vagosh2001

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top