Solved

# Specifying a Sub Range of a Range

Posted on 2010-09-21
Medium Priority
2,284 Views
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
``````
0
Question by:Carleton_H
• 3
• 2
• 2

LVL 50

Expert Comment

ID: 33731174
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

LVL 93

Expert Comment

ID: 33731175
0

LVL 93

Expert Comment

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

LVL 58

Expert Comment

ID: 33731205
I actually think it is

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

LVL 50

Expert Comment

ID: 33731208
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

LVL 58

Accepted Solution

cyberkiwi earned 2000 total points
ID: 33731209
Time to make the same correction that Patrick did :)

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

LVL 50

Expert Comment

ID: 33731228

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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
###### Suggested Courses
Course of the Month14 days, 14 hours left to enroll