CODEWITHSUNDEEP
excelvbarange
hypers
hypers

Reputation: 1250

Check whether an Excel Range represents entire row or column

I need to determine if a given Range object is actully an entire column OR entire row OR none of two.

What I do now is based on the next code which works for me.

If Range.Rows.Count = Range.EntireColumn.Rows.Count Then
...
ElseIf Range.Columns.Count = Range.EntireRow.Columns.Count Then
...
End If

I'd like to know if there's more efficient and/or elegant way of doing the same? Perhaps, some built-in property that I've overlooked?

UPD: I get the range from:

Worksheet.Range("NamedRange")

Thanks.

Upvotes: 5

Views: 3793

Answers (5)

ChrisB
ChrisB

Reputation: 3205

Since I think the OP's own question contains a great answer (for most uses) and three years later no one has posted it, here it is. I converted the same logic into these two functions:

Function IsEntireRow(ByVal Target As Excel.Range) As Boolean
    IsEntireRow = Target.Columns.Count = Target.EntireRow.Columns.Count
End Function

Function IsEntireColumn(ByVal Target As Excel.Range) As Boolean
    IsEntireColumn = Target.Rows.Count = Target.EntireColumn.Rows.Count
End Function

Upvotes: 2

Miqi180
Miqi180

Reputation: 1691

This will work in all Excel versions:

Function cellsInRange(rng As Range) As Boolean

    Dim cellsInCol As Long, cellsInRow As Long, cols As Long, count As Long, rws As Long

    With ActiveSheet
        cellsInCol = .Columns("A").Cells.count
        cellsInRow = .Rows(1).Cells.count
    End With

    With rng
        cols = .Columns.count
        count = .Cells.count
        rws = .Rows.count
    End With

    If cols = 1 And count = cellsInCol Then
        cellsInRange = True
    ElseIf rws = 1 And count = cellsInRow Then
        cellsInRange = True
    End If

End Function

Edit

If more specific details are required about the range object, the following adaptation may prove useful:

Function cellsInRange(rng As Range) As String

    Dim cellsInCol As Long, cellsInRow As Long, cols As Long, count As Long, rws As Long

    With ActiveSheet
        cellsInCol = .Columns("A").Cells.count
        cellsInRow = .Rows(1).Cells.count
    End With

    With rng
        cols = .Columns.count
        count = .Cells.count
        rws = .Rows.count
    End With

    If cols = 1 And count = cellsInCol Then
        cellsInRange = "Single column"
    ElseIf rws = 1 And count = cellsInRow Then
        cellsInRange = "Single row"
    Else
        cellsInRange = "Neither single column nor single row"
    End If

End Function

Upvotes: 2

user3598756
user3598756

Reputation: 29421

my 0,02 cents

Select Case rng.Address
    Case rng(1).EntireColumn.Address
        MsgBox "column"
    Case rng(1).EntireRow.Address
        MsgBox "row"
End Select

where rng is a variable of Range type

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

You may be able to parse rng.Address(0,0); in English (rather than VBA):

s = rng.Address(0,0)
If s contains no letters then rng contains only full rows
If s contains no numbers then rng contains only full columns

Upvotes: 1

Vasily
Vasily

Reputation: 5782

you can use something like this:

Sub somethinglikethis()

    Dim rRange As Range

    Set rRange = Range("A:A")

    With rRange
        If .Rows.Count = 1048576 And .Columns.Count < 16384 Then
            Debug.Print "Range represents Column(s)"
        ElseIf .Rows.Count < 1048576 And .Columns.Count = 16384 Then
            Debug.Print "Range represents row(s)"
        ElseIf .Rows.Count = 1048576 And .Columns.Count = 16384 Then
            Debug.Print "Range represents whole sheet"
        Else
            Debug.Print "Range represents none of row(s) or column(s)"
        End If
    End With

End Sub

the amount of rows and columns are constants in excel (depending on Excel version): enter image description here

so, you just need to count the rows and columns in the range and compare these values with default values of rows and columns

Upvotes: 1

Related Questions