Select Named range

L

Ludo

Hi,

I would like to select 2 colomns using named ranges.

I use following code but i can't understand why columns B,C,D,E & F
are selected, while i only need column A & G

Sub TestRangeSelect()
Dim LastRow As Integer
Dim MyWeek As Range
Dim MyFPY As Range
Dim rngCell As Range

LastRow = Weeknumber(Now) + 3
With ActiveSheet
.Range(.Range("A3"), .Range("A" & LastRow)).Name = "Week2"
.Range(.Range("G3"), .Range("G" & LastRow)).Name = "FPY"
End With
FirstWeekAddress = Range("A3").Address
Set MyWeek = Names("week2").RefersToRange
Set MyFPY = Names("fpy").RefersToRange
'
With ActiveSheet
.Range(MyWeek, MyFPY).Select
End With
End Sub

thanks for your time,
Ludo
 
J

Jim Cone

Re: " i can't understand why columns B,C,D,E & F are selected, while i only need column A & G"
'--

Because a range in VBA is (usually) determined by specifying the start and end of the range.
Which is what ".Range(MyWeek, MyFPY)" does.
What you want is (note the quotation marks)...

Range("A3:A20, G3:G20").Select
-or-
Range("Week2, fpy").Select
-or-
Application.Union(MyWeek, MyFPY).Select

--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html

..
..
..

"Ludo" <[email protected]>
wrote in message
Hi,
I would like to select 2 colomns using named ranges.
I use following code but i can't understand why columns B,C,D,E & F
are selected, while i only need column A & G

Sub TestRangeSelect()
Dim LastRow As Integer
Dim MyWeek As Range
Dim MyFPY As Range
Dim rngCell As Range

LastRow = Weeknumber(Now) + 3
With ActiveSheet
.Range(.Range("A3"), .Range("A" & LastRow)).Name = "Week2"
.Range(.Range("G3"), .Range("G" & LastRow)).Name = "FPY"
End With
FirstWeekAddress = Range("A3").Address
Set MyWeek = Names("week2").RefersToRange
Set MyFPY = Names("fpy").RefersToRange
'
With ActiveSheet
End With
End Sub

thanks for your time,
Ludo
 
D

Don Guillett Excel MVP

Hi,

I would like to select 2 colomns using named ranges.

I use following code but i can't understand why columns B,C,D,E & F
are selected, while i only need column A & G

Sub TestRangeSelect()
    Dim LastRow As Integer
    Dim MyWeek As Range
    Dim MyFPY As Range
    Dim rngCell As Range

    LastRow = Weeknumber(Now) + 3
    With ActiveSheet
        .Range(.Range("A3"), .Range("A" & LastRow)).Name = "Week2"
        .Range(.Range("G3"), .Range("G" & LastRow)).Name = "FPY"
    End With
    FirstWeekAddress = Range("A3").Address
    Set MyWeek = Names("week2").RefersToRange
    Set MyFPY = Names("fpy").RefersToRange
'
    With ActiveSheet
        .Range(MyWeek, MyFPY).Select
    End With
End Sub

thanks for your time,
Ludo

Don't know why you feel the need to name them but this simple macro
should do it. Also you probably do not need to select to work with the
ranges. Instead use .copy sheets("destsht").range("a33")

Option Explicit
Sub selectCandG()
Dim lr As Long
lr = Weeknumber(Now) + 3
Range("c1").Resize(lr).Name = "Colc"
Range("g1").Resize(lr).Name = "colg"
Range("colc,colg").Select
End Sub
 
D

Don Guillett Excel MVP

Don't know why you feel the need to name them but this simple macro
should do it. Also you probably do not need to select to work with the
ranges. Instead use .copy sheets("destsht").range("a33")

Option Explicit
Sub selectCandG()
 Dim lr As Long
lr = Weeknumber(Now) + 3
Range("c1").Resize(lr).Name = "Colc"
Range("g1").Resize(lr).Name = "colg"
Range("colc,colg").Select
End Sub- Hide quoted text -

- Show quoted text -
OR......
Sub selectAandG_SAS()
Dim lr As Long
lr = Weeknumber(Now) + 3
Range("a1:a" & lr & ",g1:g" & lr).Name = "ColAG"
Range("ColAG").Select
End Sub
 
L

Ludo

OR......
Sub selectAandG_SAS()
Dim lr As Long
lr = Weeknumber(Now) + 3
Range("a1:a" & lr & ",g1:g" & lr).Name = "ColAG"
Range("ColAG").Select
End Sub- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Thank you all for you great support.
What would we do witout MVP's like you all.

Regards,
Ludo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top