Re-define a range in cells

[Guest]

Hi
How do i re-define the following range in cell notation so variables can be
used for the column and row?

Dim tbl as range
Set tbl = .Range(.Range("D6"), .Range("D6" &
..Range("D65536").End(xlUp).Row))
Works fine but is fixed.

Dim rownum as integer
Dim colnum as integer
rownum=6 'initially
colnum=4 'initially

I just cannot get anything to work. The next line seemed ok at first and
produced the expected answer. But then it failed and in testing i found it
produced "D5xxxx and not D6xxxx as expected.

Set tbl = .Range(.Cells(6, colnum), .Cells(65536, colnum).End(xlUp))

I would very much appreciate some help on something which seems so simple to
convert.

T.I.A.

Geoff

 

[Guest]

Hi Geoff,

I am not sure if I completely understood what you need to do, but maybe this
could help you on the way:

nRow = 6: nCol = 4
nLastRow = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
Set tbl = Range(Cells(nRow, nCol), Cells(nLastRow, nCol))

Regards,
Herbert

 

[Guest]

Hi Herbert
Thanks for the reply, your suggestion makes for easier understanding and i
will test it in my scenario.
In the meantime I have discovered why my code failed, it is not the range -
that is correct. What is not correct - I believed if i started my search for
the last row at a specific row, i.e. D6 then .Cells(65536, 4).End(xlUp))
would not go above D6 - but I now know that it does.
In my scenario it found a title string at D5 and not an integer, it was this
that caused an error when testing for values and not the range function as i
thought at first.

Thanks again for the answer, i can put that into my code library for future
use.

Geoff