MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Printing A1 to last non blank cell in F (but may contain formula)


Posted by Danny on September 12, 2000 1:53 PM

I am currently using the following code for printing
a sheet.

Sub PrintSheet2()
Sheets("Sheet2").Select
Range("K6").Select
Selection.End(xlDown).Select
ActiveCell.Name = "mystop"
ActiveSheet.PageSetup.PrintArea = ("A1:mystop")
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

The problem is that I have the formula:

=IF($A1="","",IF(OR($B1>0,J1>0),SUM($C1*$L1)+($D1*($L1*1.5)+IF($E1="",0,$E1)+$J1),))

in the F column (the value 1 changes down the column),
so going to the last cell in the F column for a print
area does not work. How can I change the code (and
maybe clean it up a little), to set the print area to
(A1:"the last blank value in column F"). Greater than
0 won't work either because the last value in F may be 0.

Thanks in advance.
Danny


Posted by Celia on September 13, 0100 6:29 AM

Correction


Correction :-
The line that starts "Do Until cell....." should start "Do While cell....."

Celia

Posted by Danny on September 14, 0100 9:50 AM

I'm getting a Type mismatch error

Ok, I'm trying to use this exact code:

Sub PrintLedger()
Dim ws As Worksheet, cell As Range
Set ws = Sheets("PAYROLL LEDGER")
Set cell = ws.Range("K6")
Do While cell.Offset(1, 0).Value > 0 Or cell.Offset(1, 0).Value = 0
Set cell = cell.Offset(1, 0)
Loop
ws.PageSetup.PrintArea = ("A1:" & cell.Address)
ws.Printout
End Sub

And I get a runtime error '13' - Type mismatch error
on the 'Do While' line.

I've tried for days with everything else, and mixing
other things around, and can't get it. It either locks
up excel or prints only A1:K7 when I run the macro.
It doesn't seem to me like THIS code would work anyway
(but that could be my inexperience). In row 'K', there
is a formula running from K6:K70 that results in a
blank if the item to be looked up is blank, or the actual
value of the lookup. This may be '0' at several instances
throughout the column, and the last row (cell) in the
column to be printed is usually not '0'. At the current
time, I want to print A1:K55, since K55 is the last cell
in which the lookup formula finds a value in column A (this
cell changes from time to time). I don't know what to do.

Thanks for all your help, and I'll wait to hear back from you.
Danny

Posted by Danny on September 15, 0100 4:42 PM

Ok, I got it.

I changed it around a little and used column A as a reference instead of K (no difference since K56 will be blank if A56 is blank). So in case anyone wants to use it, here it is:

Sub PrintLedger()
Dim ws As Worksheet, cell As Range
Set ws = Sheets("PAYROLL LEDGER")
Set cell = ws.Range("A65536").End(xlUp)
Do Until cell.Offset(-1, 0).Value > "1"
'values are actually names but it worked.
Set cell = cell.Offset(-1, 0)
Loop
ws.PageSetup.PrintArea = _
("A1:" & cell.Offset(-1, 10).Address)
ws.PrintOut
End Sub

Thanks again Celia for all of your help.

Posted by Celia on September 12, 0100 4:43 PM

Danny
Try this :-

Sub PrintSheet5()
Dim ws As Worksheet, cell As Range
Set ws = Sheets("Sheet2")
Set cell = ws.Range("F6")
Do Until cell.Offset(1, 0).Value > 0 _
Or cell.Offset(1, 0).Value <> 0
Set cell = cell.Offset(1, 0)
Loop
ws.PageSetup.PrintArea = _
("A1:" & cell.Address)
ws.PrintOut
End Sub

Celia

Posted by Celia on September 14, 0100 5:12 PM

Re: I'm getting a Type mismatch error

Danny
Sorry, I should have tested the code first.
The "Do" line should have read :-
Do Until cell.Offset(1, 0).Value = ""

In any event, I think I misunderstood your requirement. See if the following does it. If not, send me a sample file.

Sub PrintLedger()
Dim ws As Worksheet, cell As Range
Set ws = Sheets("PAYROLL LEDGER")
Set cell = Range("K65536").End(xlUp)
Do Until cell.Value <> ""
Set cell = cell.Offset(-1, 0)
Loop
ws.PageSetup.PrintArea = ("A1:" & cell.Address)
ws.PrintOut
End Sub

Celia

Posted by Celia on September 13, 0100 5:46 PM

Correction of the correction !

Further correction:
The whole line should read :-

Do While cell.Offset(1, 0).Value > 0 _
Or cell.Offset(1, 0).Value = 0

Celia