How to declare Excel range in VBA?

You probably declare the scope of the data in your macros very often. And You want to feel convenient in every case with it. In this post I am going to present You my 5 simple ways of how to declare Excel range in VBA.

Before We start with the main thing let’s, as always, sketch up the example table. 20 numbers from 1 to 20 in 2 columns and 10 rows. See table on the right.

Range(“A1:B10”)

The first method I am going to show You is the one using cells name. I was using this mainly or only at the beginning of my journey with VBA. That was probably because I saw it in module recorded by Excel. And it goes like this:

Sub test()

Dim ws As Worksheet
Dim rng As Range

Set ws = ThisWorkbook.Sheets(1)

With ws
    
    Set rng = .Range("A1:B10")
    
End With

End Sub

Range(“A1”).CurrentRegion

My second method is really simple. It is like manual CTRL + A combination having selected at least 1 cell from the table, for instance A1.

    Set rng = .Range("A1").CurrentRegion

To use this method You need to be sure that You don’t have any empty rows or columns in your range. Unfortunately, in any blank situation your range will be set not as fully as You want. So this is not the safest method. Rather not recommended in complex macros.

Rows(1)

If You want to set only chosen row You can use third method:

    Set rng = .Rows(1)

Just put in brackets row number or range:

    Set rng = .Rows("1:2")

Importantly, remember about quotation marks and colon!

Columns(1)

Very similar situation like above, but column names are letters, not numbers. So in case of columns You can use number:

    Set rng = .Columns(1)

Or name:

    Set rng = .Columns("A")

Or range:

    Set rng = .Columns("A:B")

The same like in third method keep in mind about quotation marks and colon between column letters!

Remember also, that using Columns() or Rows() You are setting not only values from table, but values from every cell from defined column or row!

Range(Cells(1,1), Cells(10,2))

Last, but not least is method using cells coordinates. One of my favorite method and probably most used by me.

    Set rng = .Range(.Cells(1, 1), .Cells(10, 2))

I think this is also the most accurate way to declare Excel range in VBA. A bit like the first method, but in some cases easier to find coordinates with VBA functions than columns names. Also very convenient for loops.

Anyway, this is my ways of declaring ranges. Now if You feeling good with range declaration, maybe it’s time for arrays? Please get in https://simpleexcelvba.com/arrays-as-the-best-table-alternative-in-excel-vba/

How do You declare range in VBA? Got any other methods or improvements to this? If You got any comments or suggestions feel free to comment down below, I’m open for all of your ideas!