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

Carleton_HAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
Time to make the same correction that Patrick did :)

loRow.Range.Resize(,3) ----  first 3 columns, all rows
0
 
DaveCommented:
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

0
 
Patrick MatthewsCommented:
How about:loRow.Rows(1).Resize(3)
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Patrick MatthewsCommented:
Rather...loRow.Range.Rows(1).Resize(3)
0
 
cyberkiwiCommented:
I actually think it is

loRow.Resize(,3) ----  first 3 columns, all rows
0
 
DaveCommented:
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

0
 
DaveCommented:

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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.