?
Solved

Specifying a Sub Range of a Range

Posted on 2010-09-21
7
Medium Priority
?
2,284 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:Carleton_H
  • 3
  • 2
  • 2
7 Comments
 
LVL 50

Expert Comment

by:Dave Brett
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)
MsgBox rng2.Address(0, 0)

Open in new window

0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33731175
How about:loRow.Rows(1).Resize(3)
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 33731176
Rather...loRow.Range.Rows(1).Resize(3)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33731205
I actually think it is

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

Expert Comment

by:Dave Brett
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)
MsgBox rng2.Address(0, 0)

Open in new window

0
 
LVL 58

Accepted Solution

by:
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

by:Dave Brett
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)
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question