Setting Range by code

[Ashanti]

I have a table which will always have 3 columns but
anything from 2 to 2000 rows. The first row is the column
titles.

Is it possible using code to name a range to all cells in
the table with data in them.

For eg: Table has 250 Lines so range would be A2:C250

Thanks so much

 

[Frank Kabel]

Hi
try
sub foo
dim rng as range
dim lastrow as long

LastRow = ActiveSheet.Cells(Rows.count, rng.Column).End(xlUp).row
set rng = range(cells(2,1),cells(lastrow,3))
rng.select
end sub

 

[JulieD]

Hi Ashanti

you don't need VBA to create this - its known as a "dyanamic range" and can
be created following the instructions at Debra Dalgleish's (excellent)
website
http://www.contextures.com/xlNames01.html#Dynamic

the formula that you will end up with in your case will be similar to
=OFFSET(Sheet1!$A$2,0,0,Counta(Sheet1!$A:$A),3)

Cheers
JulieD

 

[Ashanti]

Hi Frank

When i try to run this i get the error message 'Object
block or with variable not set'

Do you know why this may happen?

 

[Frank Kabel]

Hi
sorry a copy+paste error. Try

Sub foo()
Dim rng As Range
Dim lastrow As Long

lastrow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row
Set rng = Range(Cells(2, 1), Cells(lastrow, 3))
rng.Select
End Sub