Re-define a range in cells

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:D" &
..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 "D5:Dxxxx and not D6:Dxxxx 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

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


"Geoff" wrote:

> 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:D" &
> .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 "D5:Dxxxx and not D6:Dxxxx 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
>

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

"Herbert" wrote:

> 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
>
>
> "Geoff" wrote:
>
> > 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:D" &
> > .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 "D5:Dxxxx and not D6:Dxxxx 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
> >