Select Named range

  • Thread starter Thread starter Ludo
  • Start date Start date
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
 
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
 
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
Click to expand...

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'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 -
Click to expand...
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
 
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 -
Click to expand...

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

Regards,
Ludo
 
Back
Top