Select Named range

[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

 

[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

 

[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

 

[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

 

[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