Putting selected range addresses in an array

SMI · July 5th, 2006, 07:27 AM

Dear all
I have a sheet where I want to store specific cell addresses separated by comma in an array and at the end I would like to use the following script:

e.g
dim myRange as string
myRange = "$A$25:$F$25,$A$27:$F$27,$A$28:$F$28"
Range(myRange).EntireRow.Delete

My objective is to store some addresses in an array (parameter array may be) variable and then put those values in my string variable as above.

The following code has some problems, also note that I tried to use join function so that individual range addresses may be separated by a comma to make a valid range as stored in my above 'myRange; variable.

' this is the loop where I am locating all the unwanted cell addresss
      For q2 = dele1 To dele2 Step 2
             Range("a" & q2 & ":f" & q2).ClearContents
             AnyNumberArgs Range("a" & q2 & ":f" & q2).Address
             ' ----> this gives error
myRange = Join(delAddresses(intI), ",")
             myRange = AnyNumberArgs
             Next q2

' This is my function that I have tried from VBA help but this does not work in this case
Function AnyNumberArgs(ParamArray delAddresses() As Variant)
    Dim intI As Integer

   'Debug.Print delAddresses(intI); " this line address"
    ' Use UBound function to determine upper limit of array.
    For intI = 0 To UBound(delAddresses())
            AnyNumberArgs = delAddresses(intI)
    Next intI

End Function

Genius is 99% perspiration and 1% inspiration

SMI · July 6th, 2006, 12:03 AM

I am very disappointed to see pace of this forum

Genius is 99% perspiration and 1% inspiration

echovue · July 6th, 2006, 12:35 AM

Hi SMI,

While I don't have an answer in your particular case, you'll find that the majority of programmers that have the answers are US based, and hence not available for another 7-8 hours. If you can hold out that long, I am positive you'll find an answer that will help you out.

All the best,

Mike

Mike
EchoVue.com

jrogers · July 6th, 2006, 02:58 AM

This solution is not exactly as you have specified, but I am not entirely sure of your exact requirements unless you post all your code.. The below loops through and adds the ranges to teh variable myRange, which I have dimentioned as a range, to return the range of addresses, you could always set another variable as a string and make it = myrange.address

Code:

Sub something()

    Dim myRange As Range

    dele1 = 2
    dele2 = 5
    Set myRange = Range("a1:f1")
    For q2 = dele1 To dele2 Step 2
        Range("a" & q2 & ":f" & q2).ClearContents
        Set myRange = Union(myRange, Range("a" & q2 & ":f" & q2))
    Next q2

    myRange.EntireRow.Delete

End Sub

please note I have hard coded dele1 and dele2 to provide an example. Let me know if I can help futher

SMI · July 7th, 2006, 04:49 PM

Thanks to Mike and Jroggers.

Jroggers, I will get back to you after trying out your syntax. Thanks again

Genius is 99% perspiration and 1% inspiration