Create excel ranges using column numbers in vba?

Question

To reference range of cells you can use Range(Cell1,Cell2), sample:

Sub RangeTest()
  Dim testRange As Range
  Dim targetWorksheet As Worksheet
  
  Set targetWorksheet = Worksheets("MySheetName")
  
  With targetWorksheet
    .Cells(5, 10).Select 'selects cell J5 on targetWorksheet
    Set testRange = .Range(.Cells(5, 5), .Cells(10, 10))
  End With
  
  testRange.Select 'selects range of cells E5:J10 on targetWorksheet
  
End Sub

Below are two solutions to select the range A1.

Cells(1,1).Select '(row 1, column 1) 
Range("A1").Select

Also check out this link;

• http://www.excel-vba.com/vba-code-2-6-cells-ranges.htm

We strongly recommend that you use Range instead of Cells to work with cells and groups of cells. It makes your sentences much clearer and you are not forced to remember that column AE is column 31.

The only time that you will use Cells is when you want to select all the cells of a worksheet. For example: Cells.Select To select all cells and then empty all cells of values or formulas you will use: Cells.ClearContents

--

"Cells" is particularly useful when setting ranges dynamically and looping through ranges by using counters. Defining ranges using letters as column numbers may be more transparent on the short run, but it will also make your application more rigid since they are "hard coded" representations - not dynamic.

Thanks to Kim Gysen

Range.EntireColumn

Yes! You can use Range.EntireColumn MSDN

dim column : column = 4

dim column_range : set column_range = Sheets(1).Cells(column).EntireColumn

Range("ColumnName:ColumnName")

If you were after a specific column, you could create a hard coded column range with the syntax e.g. Range("D:D").

However, I'd use entire column as it provides more flexibility to change that column at a later time.

Worksheet.Columns

Worksheet.Columns provides Range access to a column within a worksheet. MSDN

If you would like access to the first column of the first sheet. You would call the Columns function on the worksheet.

dim column_range: set column_range = Sheets(1).Columns(1)

The Columns property is also available on any Range MSDN

EntireRow can also be useful if you have a range for a single cell but would like to reach other cells on the row, akin to a LOOKUP

dim id : id = 12345


dim found : set found = Range("A:A").Find(id)

if not found is Nothing then
    'Get the fourth cell from the match
    MsgBox found.EntireRow.Cells(4)
end if

Here is a condensed replacement for the ConvertToLetter function that in theory should work for all possible positive integers. For example, 1412 produces "BBH" as the result.

Public Function ColumnNumToStr(ColNum As Integer) As String
Dim Value As Integer
Dim Rtn As String
    Rtn = ""
    Value = ColNum - 1
    While Value > 25
        Rtn = Chr(65 + (Value Mod 26)) & Rtn
        Value = Fix(Value / 26) - 1
    Wend
    Rtn = Chr(65 + Value) & Rtn
    ColumnNumToStr = Rtn
End Function