Hello
I hope someone will help me to solve my problem.
I have the data from column A to G (from H.. everything is blank) and
keep adding data below it. Is there any way I can run a macro to selec
the print area by finding the last row? Eg: if I have data in 16 row
and when I run the macro it should select the range A1-G16 and set th
print area. Row 17 will have no data in it.
Hope it is clear.
Thanks in Advance
Regds
Tom

--
Message posted from http://www.ExcelForum.com

-----Original Message-----
Hello
I hope someone will help me to solve my problem.
I have the data from column A to G (from H.. everything
is blank) and I
keep adding data below it. Is there any way I can run a
macro to select
the print area by finding the last row? Eg: if I have
data in 16 rows
and when I run the macro it should select the range A1-
G16 and set the
print area. Row 17 will have no data in it.
Hope it is clear.
Thanks in Advance

Try this
Sub LastNonBlankCell()
'Finds the last cell in a worksheet with an entry
(discounts formats & borders).
'Routine is very fast because it does NOT select/activate
each column or row.
Dim CurrCol As Long, CurrRow As Long
Dim Add As String

'This finds where Excel thinks the last cell is.
Add = Selection.SpecialCells(xlCellTypeLastCell).Address
'It either really is the last cell or the last cell is
inside it.
CurrCol = Range(Add).Column
CurrRow = Range(Add).Row

'TEST THE COLUMNS
'Test to see if this column is blank
CurrCol = CurrCol + 1 'ie the column one to the right
Do 'test in turn the columns to the left
CurrCol = CurrCol - 1
Loop Until Application.WorksheetFunction.counta(Columns
(CurrCol)) 0
'CurrCol is now the column with the last data in it.

'TEST THE ROWS
'now test to see if this row is blank
CurrRow = CurrRow + 1 'ie one row lower
Do 'test in turn the rows above
CurrRow = CurrRow - 1
Loop Until Application.WorksheetFunction.counta(Rows
(CurrRow)) 0
'CurrRow is now the row with the last data in it.

'position the 'last' cell
Cells(CurrRow, CurrCol).Activate

End Sub
Regds
Toms


---
Message posted from http://www.ExcelForum.com/

.

Hello Toms
Please amend accordingly:
With Worksheets("Sheet1")
..PageSetup.PrintArea = .Range("A1").CurrentRegion.Address
End With

"SMILE " a écrit dans le message de
...
Hello
I hope someone will help me to solve my problem.
I have the data from column A to G (from H.. everything is blank) and I
keep adding data below it. Is there any way I can run a macro to select
the print area by finding the last row? Eg: if I have data in 16 rows
and when I run the macro it should select the range A1-G16 and set the
print area. Row 17 will have no data in it.
Hope it is clear.
Thanks in Advance
Regds
Toms


---
Message posted from http://www.ExcelForum.com/

here is some code i use to set the print area

Range("A1").select
ActiveWorkbook.Names.Add Name:="Top50Rpt", RefersTo:= _
Range(Range("A1"),Range("A1").Offset(49,15))
ActiveSheet.PageSetup.PrintArea = "Top50Rpt"

You might have to change the code a little to something
like:
RefersTo:=Range(Range("A1"),Range("A1").end(xldown )_
..end(xltoright)

-----Original Message-----
Hello
I hope someone will help me to solve my problem.
I have the data from column A to G (from H.. everything
is blank) and I
keep adding data below it. Is there any way I can run a
macro to select
the print area by finding the last row? Eg: if I have
data in 16 rows
and when I run the macro it should select the range A1-
G16 and set the
print area. Row 17 will have no data in it.
Hope it is clear.
Thanks in Advance
Regds
Toms


---
Message posted from http://www.ExcelForum.com/

.