asked on

Excel VBA: Get a multi-area range row or column count

Problem:

I need to find out how many unique rows or columns there are in a specific range object. The range object will be composed of multiple areas and Excel will only return the count from the 1st area.

I have created the below code (which I know doesn't return unique rows or columns) ...

Function lngGetMultiAreaRangeDimensionCount(rngTargetRange As Range, xlDimensionType As XlRowCol) As Long

    Dim rngCurArea As Range
    
    On Error GoTo err_lngGetMultiAreaRangeDimensionCount
    
    ' Check that a valid range has been passed
    If rngTargetRange Is Nothing Then Exit Function
    
    ' Cycle through each area and total the row or column count
    For Each rngCurArea In rngTargetRange.Areas
    
        Select Case xlDimensionType
            Case xlRows
                lngGetMultiAreaRangeDimensionCount = lngGetMultiAreaRangeDimensionCount + rngCurArea.Rows.Count
            Case xlColumns
                lngGetMultiAreaRangeDimensionCount = lngGetMultiAreaRangeDimensionCount + rngCurArea.Columns.Count
        End Select
    
    Next
    
    Exit Function
    
err_lngGetMultiAreaRangeDimensionCount:

    ' Do nothing - zero returned

End Function

Open in new window

... which returns the correct count from all areas if none of those areas overlap in the dimension I have selected.

Example:

You select the range ($I$10,$K$17,$I$22,$H$22,$N$22,$I$28,$I$29,$J$28,$L$28,$Q$28) on a worksheet

use the following command to get the column count : lngGetMultiAreaRangeLimits(selection,xlColumns)

This returns 10 whereas it should really return 7

Using the same range but looking for rows the function will return 10 rather than 5

Now of course this is because it is simply adding up the count from each area.

Question:
How can I amend it so it only returns the count of unique rows or columns ?

I have tried to build up a range of unique items and then compare each area to see if it is unique but this generates a big delay. I am hoping someone has something faster

Saurabh Singh Teotia

Quick question here...what i understand if you have value

B1 and B2 You want to count them as 3 .. 2 for rows and 1 for column..correct me if i'm wrong??

Similarly B1 B2 and C1 C2 ...will get counted as 4 2 for rows and 2 for column is my assumptions correct??

Saurabh...

AL_XResearch

ASKER

Saurabh Singh Teotia: Sorry let me clarify:

I need to find the unique row or column count.

Example 1:

If you have the range "$B$1:$B$4,$D$13:$D$14,$B$18:$D$18" then the column count would return 3 (B, C & D) - rather than 5

Example 2:

If you have the range "$I$9:$I$11,$K$10:$K$15,$M$13:$M$18" then the row count would return 10 (rows 9 to 18) - rather than 14

Professor J

:-) Saurabh

looks like this one is not easy :-}

Saurabh Singh Teotia

So if i understand you either want to count unique row or you want to count unique column?? You don't want to count both row + column or you want to do that??

@Jim.. Nah once i understand what he is looking for this will be simple..but till that it's not easy.. :-)

Saurabh...

AL_XResearch

ASKER

Either unique columns OR rows. If you look at my example function I pass an indicator to select which dimension I want to test & return.

If is not an easy problem, even though it may appear so at first, as I have made a number of false starts to resolve it.

AL_XResearch

ASKER

And I want to avoid using :

the dictionary object (which I used in one of my solutions to quickly check if i had previously used a dimension)

cycling through every cell in the range to a) do an intersection with another area to test for reuse and b) too subtract one range from another.

The reason for the last point is that I have done this already and it is very very slow

ASKER CERTIFIED SOLUTION

Saurabh Singh Teotia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

AL_XResearch

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AL_XResearch

ASKER

Believe me it is a great deal harder without using Collections or Dictionaries as the only way to store is via an array and then searching for existence crates the slowness of an array check.

I had created a non 'advanced objects' solution but a) it had the above problem and b) it used 6/7 functions to create a string array of column names and compare them.

Saurabh Singh Teotia

Any particular reason you don't want to use Collection or dictionary??

AL_XResearch

ASKER

Only because a Dictionary means adding the 'MS Scripting runtime' reference and it is my feeling that Dictionaries have a memory / speed impact on the code.

I have used many Dictionaries in other functions for this specific project but I wanted to make something portable.

Rory Archibald

You don't need to add references - you can simply late bind the code - and a Dictionary should be faster than a Collection (and perhaps faster than an array search). You could also use simple string searches and Split like this:

Function lngGetMultiAreaRangeDimensionCount(rTarget As Excel.Range, xlDimensionType As XlRowCol) As Long

    Dim rArea                 As Excel.Range
    Dim rTemp                 As Excel.Range
    Dim sItems                As String

    Const csSep               As String = "|"

    sItems = csSep

    On Error GoTo err_lngGetMultiAreaRangeDimensionCount

    ' Check that a valid range has been passed
    If rTarget Is Nothing Then Exit Function

    ' Cycle through each area and total the row or column count
    For Each rArea In rTarget.Areas

        Select Case xlDimensionType
            Case xlRows
                For Each rTemp In rArea.Rows
                    If InStr(1, sItems, csSep & rTemp.Row & csSep, vbBinaryCompare) = 0 Then sItems = sItems & rTemp.Row & csSep
                Next rTemp
            Case xlColumns
                For Each rTemp In rArea.Columns
                    If InStr(1, sItems, csSep & rTemp.Column & csSep, vbBinaryCompare) = 0 Then sItems = sItems & rTemp.Column & csSep
                Next rTemp
        End Select

    Next

    lngGetMultiAreaRangeDimensionCount = UBound(Split(Mid$(sItems, 2), csSep))

    Exit Function

err_lngGetMultiAreaRangeDimensionCount:

    ' Do nothing - zero returned

End Function

Open in new window

AL_XResearch

ASKER

Rory: Thanks for the response.

Yes what you say is true although a late-binding reference still means you are dependent on an external DLL (which is what I was trying to avoid if possible).

Also usually I try to steer clear of late-binding as from a coding 'best practice' point-of-view this is a bad idea as:

you will not have the benefit of intellisense and have therefore lost that initial basic code validation

the computer can not optimize code as efficiently with late-bound variables.

as I mentioned above you are still dependent on an external object (DLL) even though the compiler will not pick it up and correct any method or property typos.

As the computer has to create a generic 'object' variable it wil usually take more memory than an early-binding var.

Always best to declare all variables and use early-binding. It also shows you have thought the code through properly.

Saurabh gave me what I was looking for. A little annoying though as I should have thought of something simple like that - just shows you can get too wrapped up in a problem to see the obvious. !

Rory Archibald

You don't have to develop the code late bound. You develop using early binding then make any necessary adjustments before release.

I don't see any correlation between binding context and whether or not you've thought the code through. In many situations late binding is extremely practical and preferable to alternatives.

Still, all moot here.

PS My preference would still be to loop through the rows/columns of each area, rather than every cell.

AL_XResearch

ASKER

My own comment just includes a slightly more polished version of the excellent code provided by Saurabh.