MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Code to stop print loop at 1st blank cell

Posted by Gregg on March 03, 2001 8:16 AM

Would appreciate some help with macro code. Have macro to print all 250 rows on spreadsheet, but sometimes I only need to print 25 rows. I would like to temporarily clear a cell (i.e., row 26) so that the print macro would stop at row 25. Hope this is as easy as it sounds, but I am new to macros so I haven't been able to figure it out.
Thanks in advance,

Posted by Roger Redhat on March 04, 2001 11:52 AM

If you use this code it will set the print area to A1:F & whatever row contains a blank cell in column A.


Sub PrintMacro()
Dim r As Integer, NumberOfColumns As Integer
Dim PrintRange As Range
'Determine print range
r = 0
Do Until Cells(r + 1, 1).Value = ""
r = r + 1

NumberOfColumns = 6 'Change this depending on how many
'columns you want printed.

Set PrintRange = ActiveSheet.Range(Cells(1, 1), Cells(r, NumberOfColumns))
With ActiveSheet.PageSetup
.PrintArea = PrintRange.Address
.FitToPagesTall = 1
.FitToPagesWide = 1
End With

End Sub

Posted by Gregg on March 04, 2001 12:39 PM

Roger - Thanks & A Tip of the hat to you!

It did the trick!!