MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Sort Column

  Asked By: Lisa    Date: Nov 06    Category: MS Office    Views: 837

I have finance spreadsheets i.e. P&L's for different countries. Each
of the spreadsheets has a different last column which has data in it.
These reports are generated from our finance reporting tool. Instead
of manually setting a print range, is there a way of using VBA to set
the range which takes into account the last column with data. The last
row in all cases should be "710".[egs of print areas : .PrintArea
= "$A$1:$BM$710" ; .PrintArea = "$A$1:$BA$710"]
I don't know what the print range will be until the report has been
ran. I wish to attach the "macro" to the blank template which is used
by the reporting tool. When a country specific report is ran the
relevant columns will be "populated".



2 Answers Found

Answer #1    Answered By: Vickie Smith     Answered On: Nov 06

I'm not sure, but I think what you're asking is to identify the last
column and then set the print  area accordingly. I understood that
your "last row" is set already at 710, but in case you need to
identify the last row and column  (i.e. the lastcell used on the
spreadsheet), here is one option:

Sub FindLastCell()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
MsgBox Cells(LastRow, LastColumn).Address
End If
End Sub

Then, set the print area with:
Range(Cells(1, 1), Cells(LastRow, LastColumn)).Select
ActiveSheet.PageSetup.PrintArea = Selection.Address

Answer #2    Answered By: Lois Schmidt     Answered On: Nov 06

After re-reading what I had written:

. . . it would be therefore easy to hardcopy set the LastRow as
710, so the resulting Printarea code would be:

Range(Cells(1, 1), Cells(710, LastColumn)).Select
ActiveSheet.PageSetup.PrintArea = Selection.Address

--as for setting  the macro up on the template, it would make more
sense to me to have it on the same workbook as the code which does
the sort  in the first place, and have it fire whenever the sort is
done for each page.

Didn't find what you were looking for? Find more on Sort Column Or get search suggestion and latest updates.