Solved

Specifying a Sub Range of a Range

Posted on 2010-09-21
7
2,169 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 92

Expert Comment

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

Expert Comment

by:Patrick Matthews
ID: 33731176
Rather...loRow.Range.Rows(1).Resize(3)
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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 500 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

729 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