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
lngGetMultiAreaRangeDimensionCount = lngGetMultiAreaRangeDimensionCount + rngCurArea.Rows.Count
lngGetMultiAreaRangeDimensionCount = lngGetMultiAreaRangeDimensionCount + rngCurArea.Columns.Count
' Do nothing - zero returned
... which returns the correct count from all areas if none of those areas overlap
in the dimension I have selected.
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.
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