Need Code For Selection

  • Thread starter Thread starter SMILE
  • Start date Start date
S

SMILE

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
 
-----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
Click to expand...

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
 
Hello Toms
Please amend accordingly:
With Worksheets("Sheet1")
..PageSetup.PrintArea = .Range("A1").CurrentRegion.Address
End With
 
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)
 
Back
Top