Array Prefix & Suffix Methods

Hello folks,

a quick one

in my array I am listing ranges.

so far i have come up with this but it does look verbose.

Code:

---


  Sub Array_Tasks()
 
    Dim x: x = "7:"
    Dim y: y = 50
   
 
    oArray = Array("K" & x & "K" & y, "L" & x & "L" & y)
   
    ' which would be  "K7:K50"      "L7:L50"
   
 
  End Sub

---

is there an eaiser way to make the ranges in an array?

Depends on what you're trying to do

Code:

---

Option Explicit
Sub Array_Tasks_1()
    Dim aryRanges() As Variant
    Dim x As String
    x = "7:"
    Dim y As String
    y = "50"
   
   
    aryRanges = Array(Range("K" & x & "K" & y), Range("L" & x & "L" & y))
   
    MsgBox aryRanges(0).Address
    MsgBox aryRanges(1).Address
   
    aryRanges(0).Interior.Color = vbGreen
    aryRanges(1).Interior.Color = vbRed
   
End Sub

---

Code:

---

  Dim x: x = "7:"
    Dim y: y = 50 
   
    oArray = Array("K" & x & "K" & y, "L" & x & "L" & y)

---

Those aren't Ranges, those are Range.Addresses with variables, that's how you do it.

For an array of Ranges, Pauls method does that in one step.

I prefer to explicitly Set the Ranges, then assign them to the Array

Code:

---

Set Range_1 = Range("K" & x & ":K" & y)
Set Range_2 = Range("L" & x & ":L" & y)

oArray = Array(Range_1, Range_2)

---

If I only a two column Range like that

Code:

---

oRange <=> oArray
Set oRange = Range("K" & x & ":L" & y)

---

I LIKE verbose code :ipray:

Thank you Paul

I was thinking

Code:

---


Sub Array_Tasks_1()
    Dim aryRanges() As Variant
    Dim x As String
    x = "7:"
    Dim y As String
    y = "50"
   
   
    aryRanges = Array(Range("K" & x & "K" & y), Range("L" & x & "L" & y))
   
   
   
    For i = LBound(aryRanges) To UBound(aryRanges)
   
      aryRanges(i).Interior.Color = vbGreen
 
 
    Next i
   
   
End Sub

---

may be do something to the ranges in a for loop

I have a lot of ranges to list and the constant number I change and then i have to manually type each one, so i thought an array constant number would help as laid above, so i can change my x and y and not have to type each one

Thank you Sam,

I am happy to learn something new every time

Set Range_1 = Range("K" & x & ":K" & y)
Set Range_2 = Range("L" & x & ":L" & y)

oArray = Array(Range_1, Range_2)


i can do this too, if the verbosity ovewhelms me - we ll they do look like those fireworks about to go off :grinhalo:

Ok let me play about , have a feeling my for loop will moan about the ranges not set up correctly and something about the array not being declared, thinking out loud

thank you gentlemen, ill be back later

good evening

It's still not clear to me what you want to do, but this is one way to have Excel generate range variables for you in a macro

The collection key is the first cell ('header') in the column, and the range is one row below that one to the end of data in the column

Code:

---

Option Explicit
Dim rngCollection As Collection

Sub demo()
    Dim r As Range, c As Range
   
    Set rngCollection = New Collection
   
    For Each r In Range("C4:G4").Cells      '  can be any row
        rngCollection.Add Range(r.Offset(1, 0), r.End(xlDown)), r.Value
    Next
   
   
    rngCollection("First").Interior.Color = vbRed
   
    rngCollection("Last").Interior.Color = vbGreen
   
    MsgBox rngCollection("Street").Count

    For Each c In rngCollection("Something").Cells
        MsgBox c.Address & " -- " & c.Value
    Next
End Sub

---

Code:

---

Sub M_snb()
  with range("K7:K50")
    sn=array(.0ffset, .offset(,1), .offset(,2))
    sp=array(.address,.offset(,1).address,.offset(,2).address)
  end with   
End Sub

---

Thank you for your help Paul,

my foray into Collections has been extremely limited.

I am still to get the hang of arrays, as many a mishap always happns.

So Collections are reallly useful for naming ranges on the go , and the column header does that for me.


I can generate large amounts of ranges without worrying about - the way i was doing it before prefix and suffixing it, that was a long way.

and i hate to have to type in one by one the ranges, as i change my mind like the wind, and maths is not my wizadry

well all is good

Good Sunday Paul and folks

and thanks again for all the help