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
LVL 3
AL_XResearchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
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...
0
AL_XResearchAuthor Commented:
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
0
ProfessorJimJamCommented:
:-)   Saurabh

looks like this one is not easy :-}
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Saurabh Singh TeotiaCommented:
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...
0
AL_XResearchAuthor Commented:
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.
0
AL_XResearchAuthor Commented:
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
0
Saurabh Singh TeotiaCommented:
Use this function...

Function getcount(r As Range, str As String)
    Dim col As Collection
    Dim cell As Range
    Set col = New Collection
    If UCase(Trim(str)) = "R" Then
        On Error Resume Next
        For Each cell In r
            col.Add cell.Row, CStr(cell.Row)
        Next cell
    ElseIf UCase(Trim(str)) = "C" Then
        On Error Resume Next
        For Each cell In r
            col.Add cell.Column, CStr(cell.Column)
        Next cell
    End If
  getcount = col.Count

End Function

Open in new window


Enclosed is the workbook for your reference where i have highlighted what you are looking for One for Row and other for Column..

Saurabh...
Unique-Col---Row-Count.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AL_XResearchAuthor Commented:
Excellent ! I had forgotten all about Collection (as it is the poorer cousin of Dictionary) but that is exactly what I am looking for.

I would still ideally like a solution that doesn't use collections, dictionaries etc (ideally Excel would have it built in) but at least there are no additional external references to install.

I hope you don't mind but I have taken the liberty of simplifying and making the function 'production ready' so it can be used easily and handles errors. I have added it below so others can benefit too:

Function lngGetUniqueRangeDimensionCount(rngTarget As Range, xlDimensionType As XlRowCol)

    Dim colUniqueItems As Collection
    Dim rngCurCell As Range
    
    On Error GoTo err_lngGetUniqueRangeDimensionCount
    
    ' Init. var.
    Set colUniqueItems = New Collection
    
    ' Validate param.
    If rngTarget Is Nothing Then
        lngGetUniqueRangeDimensionCount = 0
        Exit Function
    End If
        
    ' Cycle through and add unique members to the collection
    For Each rngCurCell In rngTarget.Cells
    
        Select Case True
            Case xlDimensionType = xlRows
                colUniqueItems.Add rngCurCell.Row, CStr(rngCurCell.Row)
            Case xlDimensionType = xlColumns
                colUniqueItems.Add rngCurCell.Column, CStr(rngCurCell.Column)
        End Select
        
    Next rngCurCell
        
    ' Return the count
    lngGetUniqueRangeDimensionCount = colUniqueItems.Count
    
    Exit Function
    
err_lngGetUniqueRangeDimensionCount:
    
    ' Test for error were the collection already contains that key
    If Err.Number = 457 Then Resume Next
    
    ' All other errors will cause the function to return -1 (to indicate error)
    lngGetUniqueRangeDimensionCount = -1

End Function

Open in new window

0
AL_XResearchAuthor Commented:
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.
0
Saurabh Singh TeotiaCommented:
Any particular reason you don't want to use Collection or dictionary??
0
AL_XResearchAuthor Commented:
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.
0
Rory ArchibaldCommented:
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

0
AL_XResearchAuthor Commented:
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. !
0
Rory ArchibaldCommented:
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.
0
AL_XResearchAuthor Commented:
My own comment just includes a slightly more polished version of the excellent code provided by Saurabh.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.