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")

'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
``````
Who is Participating?

Commented:
Time to make the same correction that Patrick did :)

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

Commented:
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)
``````
0

Commented:
0

Commented:
Rather...loRow.Range.Rows(1).Resize(3)
0

Commented:
I actually think it is

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

Commented:
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)
``````
0

Commented:

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)

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