Excel VBA equivalent of a range definition

John Carney
John Carney used Ask the Experts™
on
This range definition (LastCol) gives me the last column of my 11-column wide Table1:

=OFFSET(Table1,0,10,ROWS(Table1),COLUMNS(Table1)-10)

How do I express that in VBA so that I don't have to create and name the range before running a macro that relies on it?

Thanks,
John
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
If you already have a range named Table1, have you tried:

Set MyLastColumn = Range("Table1").Columns(11)
Top Expert 2008
Commented:
Otherwise, if you don't have the range, and you data is a block (i.e. no empty lines or rows) starting row 1, you could use

dim lLastCol as long, lLastRow as long, MyLastColumn as range

lLastCol=cells(1,columns.count).end(xltoleft).column
lLastRow=cells(rows.count,llastcol).end(xlup).row

Set MyLastColumn =range(cells(1,llastcol),cells(llastrow,llastcol))
John CarneyReliability Business Tools Analyst II

Author

Commented:
I wasn't expecting the answer to be so simple and common sense logical. Thanks, Patrick.

And thanks Thomas for a way to approach it when there's no pre-defined range to play off of.

- John
Top Expert 2010

Commented:
Glad to help.  BTW, if you always want the last column, but are not sure which column number that would be...

With Range("Table1")
    Set MyLastColumn = .Columns(.Columns.Count)
End With

Open in new window

Top Expert 2008

Commented:
Glad to see you're making progress on the VBA front, John.

Thomas

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial