asked on

Specifying a Sub Range of a Range

How do I select a sub range of a range? The last line of this code obviously causes an error, but it shows what I need it to do:

Dim loLegendTemplate As ListObject
Dim loRow As ListRow
  
Set loLegendTemplate = Worksheets("Templates").ListObjects("tbLegendTemplate")
Set loRow = loLegendTemplate.ListRows.Add

'There are 4 columns in loRow.Range
'How do I select the first three columns only?  For example:
loRow.Range(1-3).BorderAround Weight:=xlThin

Open in new window

Dave

One way would be to set an intersect of the first three columns from the first cell in the range (ie rng1.Cells(1)) combined with the actual range

This code returns A1:C:20 from a master of A1:E20

Cheers

Dave

Set rng1 = Range("A1:E20")
Set rng2 = Intersect(rng1.Cells(1).Resize(1, 3).EntireColumn, rng1)
MsgBox rng2.Address(0, 0)

Open in new window

Patrick Matthews

How about:loRow.Rows(1).Resize(3)

Patrick Matthews

Rather...loRow.Range.Rows(1).Resize(3)

cyberkiwi

I actually think it is

loRow.Resize(,3) ---- first 3 columns, all rows

Dave

Carleton_H

the Intersect approach is useful if the range has multiple areas. ie it caters for this below

Patrick,

I couldn't get that to work? :)

Cheers

Dave

Set rng1 = Range("A1:E10,A15:E20")
Set rng2 = Intersect(rng1.Cells(1).Resize(1, 3).EntireColumn, rng1)
MsgBox rng2.Address(0, 0)

Open in new window

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dave

Yep, that synatx makes sense. Although it works for a continuous range - not with multiple areas

first code below is fine

second fails

Cheers

Dave

'1
Set rng1 = Range("A1:E20")
Set rng2 = rng1.Resize(, 3)
MsgBox rng2.Address(0, 0)


'2
Set rng1 = Range("A1:E10, A15:E20")
Set rng2 = rng1.Resize(, 3)
MsgBox rng2.Address(0, 0)

Open in new window