VBA - convert array of cell addresses to single range

VBA - convert array of cell addresses to single range

Post by Phil Bew » Sun, 29 Sep 2002 07:30:07



I have several cell addresses stored in a VBA array:

    { B2 B5 C3 C5 C4 C2 B4 B3 }

Does anyone have a function that can turn this into
the single reference

    B2:C5

It is possible that cell addresses will be R1C1-style
instead of $A$1-style.  It is also possible that
there will be multiple discontiguous ranges within
the array.

Thanks,

Phil

 
 
 

VBA - convert array of cell addresses to single range

Post by Jim Co » Tue, 01 Oct 2002 02:49:17


Phil,

The following code should get you started...

'------------------------------
Function ParseArray() As String
  Dim MyArray As Variant
  Dim N1 As Long
  Dim N2 As Long
  Dim EndAddress As String
  Dim StartAddress As String

  MyArray = Array("B2", "B5", "C3", "C5", "C4", "C2", "B4", "B3")

  'Sort the array ...
  'The following Microsoft Knowledge Base Articles have VBA code to sort arrays:
  '  Q133135 - xl 97 and earlier
  '  Q213818 - xl 2000
  'MyArray, after sorting will be...

  MyArray = Array("B2", "B3", "B4", "B5", "C2", "C3", "C4", "C5")

  N1 = LBound(MyArray)
  N2 = UBound(MyArray)
  StartAddress = MyArray(N1)
  EndAddress = MyArray(N2)

  ParseArray = Range(StartAddress, EndAddress).Address
  MsgBox ParseArray
End Function
'-----------

You can run the function as is to see what you get.
The discontiguous range and R1C1 questions are not answered by the above.

Jim Cone
San Jose, CA

******************************************************


> I have several cell addresses stored in a VBA array:

>     { B2 B5 C3 C5 C4 C2 B4 B3 }

> Does anyone have a function that can turn this into
> the single reference
>     B2:C5
> It is possible that cell addresses will be R1C1-style
> instead of $A$1-style.  It is also possible that
> there will be multiple discontiguous ranges within
> the array.
 Thanks,

> Phil


 
 
 

VBA - convert array of cell addresses to single range

Post by Leo Heuse » Tue, 01 Oct 2002 05:55:49


Phil

This UDF will get you started, assuming the references in
a range in the worksheet. A mixture of A1-style and
R1C1-style is not allowed.
The function returns the reference in A1-style.

E.g. your references B2 B5 C3 C5 C4 C2 B4 B3 in
A2:A9, B2 in A1, B5 in A3 etc. use this expression
from the sheet:

=toranges(a2:a9,1)
because the references in A2:A9 are in A1-style.

If they had been in R1C1-style the call would have been:

=toranges(a2:a9,0)

--
Best regards
Leo Heuser
MVP Excel

Function ToRanges(FromRange As Range, IsA1 As Boolean) As String
'Leo Heuser, 28-09-2002
Dim Counter As Long
Dim Counter1 As Long
Dim Dummy As String
Dim RangeData As Variant
Dim ResultRange As Range

    RangeData = FromRange.Value

    ToRanges = "Fault!"

    For Counter = LBound(RangeData, 1) To UBound(RangeData, 1)
        For Counter1 = LBound(RangeData, 2) To UBound(RangeData, 2)
            If Not IsA1 Then
                Dummy = Mid$(Application.ConvertFormula("=" & _
                    RangeData(Counter, Counter1), xlR1C1, xlA1), 2)
            Else
                Dummy = RangeData(Counter, Counter1)
            End If

            If ResultRange Is Nothing Then
                Set ResultRange = Range(Dummy)
            Else
                Set ResultRange = Union(ResultRange, Range(Dummy))
            End If
        Next Counter1
    Next Counter

    ToRanges = ResultRange.Address

End Function



Quote:> I have several cell addresses stored in a VBA array:

>     { B2 B5 C3 C5 C4 C2 B4 B3 }

> Does anyone have a function that can turn this into
> the single reference

>     B2:C5

> It is possible that cell addresses will be R1C1-style
> instead of $A$1-style.  It is also possible that
> there will be multiple discontiguous ranges within
> the array.

> Thanks,

> Phil

 
 
 

VBA - convert array of cell addresses to single range

Post by Phil Bew » Tue, 01 Oct 2002 06:58:03


Well, yes.  The basic algorithm is to sort by row then
column, iterate through the sorted array to coalesce
like rows, then iterate through the result to coalesce
like columns.  (I'm not sure I said that right, but
you know what I mean.)  R1C1 and $A$1 references can
be handled by preprocessing before the first sort.
There can be multiple "right" answers in the case of
adjacent ranges of differing bounds, depending on
whether you proceed in row-major or column-major order.
Actually, as I think about it there is also a third
dimension where a range could extend across worksheets.
I was just hoping someone had already done it.  In
fact, I was hoping there was some method in the Excel
object model that does this, since Excel must have
the occasion for this kind of processing itself.  Is
there some kind of Range.Union method?  Or some way
to canonicalize (is that a word) the printed represen-
tation of a range?

Thanks for the pointers to sorting algorithms.  I am
new to VBA, and still a little bit in shock that the
language doesn't provide a built-in or library sort
procedure.

Thanks,

Phil


> The following code should get you started...
>   'The following Microsoft Knowledge Base Articles have VBA code to sort arrays:
>   '  Q133135 - xl 97 and earlier
>   '  Q213818 - xl 2000

> > I have several cell addresses stored in a VBA array:
> >     { B2 B5 C3 C5 C4 C2 B4 B3 }
> > Does anyone have a function that can turn this into
> > the single reference
> >     B2:C5
> > It is possible that cell addresses will be R1C1-style
> > instead of $A$1-style.  It is also possible that
> > there will be multiple discontiguous ranges within
> > the array.

 
 
 

VBA - convert array of cell addresses to single range

Post by Howard Kaiko » Tue, 01 Oct 2002 16:28:19


The sort algorithms in the referenced MSFT KB articles are very poor.
John Walkenbach's book gives an implementation of QuickSort, a much better
algorithm.

--
http://www.standards.com/; Programming and support for  Word macros,
including converting from WordBasic to VBA; Technical reviewing; Standards;
Product functional/design/specifications
------------------------------------------------

> Phil,

> The following code should get you started...

> '------------------------------
> Function ParseArray() As String
>   Dim MyArray As Variant
>   Dim N1 As Long
>   Dim N2 As Long
>   Dim EndAddress As String
>   Dim StartAddress As String

>   MyArray = Array("B2", "B5", "C3", "C5", "C4", "C2", "B4", "B3")

>   'Sort the array ...
>   'The following Microsoft Knowledge Base Articles have VBA code to sort
arrays:
>   '  Q133135 - xl 97 and earlier
>   '  Q213818 - xl 2000
>   'MyArray, after sorting will be...

>   MyArray = Array("B2", "B3", "B4", "B5", "C2", "C3", "C4", "C5")

>   N1 = LBound(MyArray)
>   N2 = UBound(MyArray)
>   StartAddress = MyArray(N1)
>   EndAddress = MyArray(N2)

>   ParseArray = Range(StartAddress, EndAddress).Address
>   MsgBox ParseArray
> End Function
> '-----------

> You can run the function as is to see what you get.
> The discontiguous range and R1C1 questions are not answered by the above.

> Jim Cone
> San Jose, CA

> ******************************************************



- Show quoted text -

Quote:> > I have several cell addresses stored in a VBA array:

> >     { B2 B5 C3 C5 C4 C2 B4 B3 }

> > Does anyone have a function that can turn this into
> > the single reference
> >     B2:C5
> > It is possible that cell addresses will be R1C1-style
> > instead of $A$1-style.  It is also possible that
> > there will be multiple discontiguous ranges within
> > the array.
>  Thanks,

> > Phil

 
 
 

VBA - convert array of cell addresses to single range

Post by Phil Bew » Wed, 02 Oct 2002 00:41:15


Very nice!  And exactly what I was looking for.  Programming Excel/VBA
sometimes makes me feel like a medieval alchemist, for whom impossible
tasks are made simple by knowing the magic incantation.

Many thanks,

Phil


> Phil

> This UDF will get you started, assuming the references in
> a range in the worksheet. A mixture of A1-style and
> R1C1-style is not allowed.
> The function returns the reference in A1-style.

> E.g. your references B2 B5 C3 C5 C4 C2 B4 B3 in
> A2:A9, B2 in A1, B5 in A3 etc. use this expression
> from the sheet:

> =toranges(a2:a9,1)
> because the references in A2:A9 are in A1-style.

> If they had been in R1C1-style the call would have been:

> =toranges(a2:a9,0)

> --
> Best regards
> Leo Heuser
> MVP Excel

> Function ToRanges(FromRange As Range, IsA1 As Boolean) As String
> 'Leo Heuser, 28-09-2002
> Dim Counter As Long
> Dim Counter1 As Long
> Dim Dummy As String
> Dim RangeData As Variant
> Dim ResultRange As Range

>     RangeData = FromRange.Value

>     ToRanges = "Fault!"

>     For Counter = LBound(RangeData, 1) To UBound(RangeData, 1)
>         For Counter1 = LBound(RangeData, 2) To UBound(RangeData, 2)
>             If Not IsA1 Then
>                 Dummy = Mid$(Application.ConvertFormula("=" & _
>                     RangeData(Counter, Counter1), xlR1C1, xlA1), 2)
>             Else
>                 Dummy = RangeData(Counter, Counter1)
>             End If

>             If ResultRange Is Nothing Then
>                 Set ResultRange = Range(Dummy)
>             Else
>                 Set ResultRange = Union(ResultRange, Range(Dummy))
>             End If
>         Next Counter1
>     Next Counter

>     ToRanges = ResultRange.Address

> End Function



> > I have several cell addresses stored in a VBA array:

> >     { B2 B5 C3 C5 C4 C2 B4 B3 }

> > Does anyone have a function that can turn this into
> > the single reference

> >     B2:C5

> > It is possible that cell addresses will be R1C1-style
> > instead of $A$1-style.  It is also possible that
> > there will be multiple discontiguous ranges within
> > the array.

> > Thanks,

> > Phil

 
 
 

VBA - convert array of cell addresses to single range

Post by Phil Bew » Wed, 02 Oct 2002 00:55:38



> The sort algorithms in the referenced MSFT KB articles are very poor.
> John Walkenbach's book gives an implementation of QuickSort, a much better
> algorithm.


> >   'The following Microsoft Knowledge Base Articles have VBA code to sort
>  arrays:
> >   '  Q133135 - xl 97 and earlier
> >   '  Q213818 - xl 2000

Ugh.  SelectionSort and BubbleSort are O(n*n), where QuickSort is O(n log n)
expected and HeapSort is O(n log n) worst case (and can be tuned so it's as
good as QuickSort, though most algorithms textbooks don't mention this -- as
you can tell, HeapSort is my favorite algorithm for sorting arrays).  See
www.microsoft.com/officedev/articles/movs102.htm for a better description of
sorting, also from Microsoft.

Does VBA offer any way to pass the comparison function to the sorting
subroutine?  Or must a program that sorts different arrays with different
comparison functions duplicate the sorting code?

Phil

 
 
 

VBA - convert array of cell addresses to single range

Post by Harlan Grov » Wed, 02 Oct 2002 01:18:33


Quibble: OP didn't mention the dimensionality of the array of addresses.
Best to use a 'For Each' loop to iterate through the array since order isn't
important.
 
 
 

VBA - convert array of cell addresses to single range

Post by Leo Heuse » Wed, 02 Oct 2002 02:08:14


You're welcome and thanks for the feedback!



Quote:> Very nice!  And exactly what I was looking for.  Programming Excel/VBA
> sometimes makes me feel like a medieval alchemist, for whom impossible
> tasks are made simple by knowing the magic incantation.

> Many thanks,

> Phil

 
 
 

1. 123r5 Getting col# from an address in single cell range?

I am trying to obtain a column # from an address entered as text (ie:
$A:$C$5)in a single cell named "PIVOT". If I type the address in the


"ERR". I have tried many different ways to do this using various cell
references and concatenations but every time I try to use an indirect
reference to the address I get an "ERR" message. I know this can't be
difficult but it has ceased being fun. If you can help please e-mail as
well as post.

                                                        Thanks

                                                                Jon

2. Primary DNS server

3. Range times single cell - easy in Excel, how in Lotus?

4. Avantgo error message. Help please

5. VBA Question - Optimizing Excel Range to VB Array

6. canon bj10-ex - looking for win9* driver

7. Excel95: Using range from dialog as array in VBA

8. Remote Admin of SBS

9. XL5 VBA problem: Reading values from a range into an array

10. Range array problem (VBA, XL5)

11. AutoComplete returns valid cell reference to VBA but range.value is empty?

12. VBA - Variable Cell Range Question