VBA: determination of a range

[Nelson78]

Hello everybody.

I have to determine a range in the following situation:

Dim lr As Long, lc As Long
Dim rng As Range

    lr = sheet.Cells(Rows.Count, 1).End(xlUp).Row
    lc = sheet.Cells(i, 1).End(xlToRight).Column
        If lc >= 16 Then lc = 15
   
   
   set rng = ?????

For example:
- the beginning cell of the range (top on the left) is D2
- suppose lr = 10
- suppose lc = 15
- rng shoud be ("D2:O10")

How can I figure it out?

Thank's.

 

[RoryA]

set rng = sheet.range("D2", sheet.cells(lr, lc))

should do the trick.

 

[Nelson78]

set rng = sheet.range("D2", sheet.cells(lr, lc))

should do the trick.

Yes, perfect.

Thank you.

 

[RoryA]

Glad to help.