CODEWITHSUNDEEP
excelvba
spinjector
spinjector

Reputation: 3515

How can Excel VBA range variables be tested for references to entire columns..?

What ways are there to test an Excel VBA range variable for references to entire columns?

I'm using Excel 2007 VBA, iterating through Range variables with For-Each loops. The ranges are passed into the function as parameters. References to individual cells, ranges of cells, and entire rows are fine.

For instance, these are okiedokie:

Range("A1")     'One cell
Range("A1:D4")  'Range of cells.
Range("10:20")  'Entire rows 10 through 20.

But if any of the ranges have references to entire columns, it will drag the function down to a screeching halt. For instance, these are not okiedokie, and they need to be tested for and avoided:

Range("A:A")
Range("A:Z")
Range("AA:ZZ")

There are a few ways I've throught of to do this, each of them plausible but with weaknesses. The code contains loops which are used for searching through cells in worksheets with many thousands of rows, so speed is critical.

Here are three ways I can think of, but I'd like to know if there are others..?

  1. The simplest & fastest method is to count the rows. If Range(x).Rows.Count=1048576, that's the maximum number of rows in a worksheet. However, this wouldn't work if the actual number of rows turned out to be exactly that number, or if by some wild chance there were multiple overlapping areas/ranges that all added up to that number. Both unlikely, but possible. Also, if the version of Excel changes, so might that number, thus rendering the code broken.

  2. Use a RegEx match against the text of Range.Address(False,False) with a pattern such as ([A-Z]{1,3}):([A-Z]{1,3}). I think this would be a medium on the speed scale.

  3. Use VBA loops, If-Then, and string functions such as InStr() and Mid() to pick at the text of Range.Address(False,False). I think this would be the slowest possible way to do it.

Upvotes: 2

Views: 281

Answers (3)

spinjector
spinjector

Reputation: 3515

Ok, after reading everyone's suggestions, I realized that no matter what I do, any Range objects passed to my function might include either an entire column reference or any combination of overlapping Range references that result in an entire column being selected.

But in translation, that means...all rows in the data, aka the UsedRange. It's possible with a large amount of data the UsedRange may actually hit the last row at 1048576. And any combination of Range references passed to my Function might result in a huge area that does cover an entire column, all the way to the maximum row.

Of course the likelihood of that happening is very low, but I do like to cover all bases in my code. But the key to this puzzle is UsedRange. This creates a "synthetic maximum last row". If the GrandRange, for lack of a better name, covers all rows in the UsedRange, then my function has nothing to do and no data to return. And so a simple IF-Then-Exit should give me the solution I was looking for:

If Intersect(UsedRange,LeGrandeRange).Rows.Count = UsedRange.Rows.Count Then
    'All rows in `UsedRange` are affected.
    'Nothing to do.
    Exit Function
Else
    'Do everything here.
    'Then exit normally.
    ...
    ...
    ...
Endif

Upvotes: 0

stew
stew

Reputation: 31

Not sure I understand the question completely but this might work for you:

Public Sub Test()
    Debug.Print RowCheck(ThisWorkbook.Worksheets("Sheet1").Range("A1:A10"))
End Sub

Public Function RowCheck(InputRange As Range)
    Dim u As Long 'used number of rows
    Dim x As Long 'max number of rows for any column
    Dim r As Long 'number of rows based on input range

    With InputRange
        u = Cells(Rows.Count, .Columns(1).Column).End(xlUp).Row
        r = .Rows.Count
        x = Rows.Count
    End With

    If r = x And u < r Then
        RowCheck = "A bad column reference provided"
    Else
        RowCheck = "This is a valid reference"
    End If
End Function

Upvotes: 1

R. Roe
R. Roe

Reputation: 609

You could test if the range is a reference to a column by checking the Range.Address against the Range.EntireColumn.Address like this:

If Range("AA:ZZ").Address = Range("AA:ZZ").EntireColumn.Address Then
    'This returns True
End If

If Range("AA1:ZZ4").Address = Range("AA1:ZZ4").EntireColumn.Address Then
    'This returns False
End If

Upvotes: 1

Related Questions