Create excel ranges using column numbers in vba?

How is it possible to create a range in vba using the column number, rather than letter?

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

In case you were looking to transform your column number into a letter:

Function ConvertToLetter(iCol As Integer) As String
    Dim iAlpha As Integer
    Dim iRemainder As Integer
    iAlpha = Int(iCol / 27)
    iRemainder = iCol - (iAlpha * 26)
    If iAlpha > 0 Then
        ConvertToLetter = Chr(iAlpha + 64)
    End If
    If iRemainder > 0 Then
        ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
    End If
End Function

This way you could do something like this:

Function selectColumnRange(colNum As Integer, targetWorksheet As Worksheet)
    Dim colLetter As String
    Dim testRange As Range
    colLetter = ConvertToLetter(colNum)
    testRange = targetWorksheet.Range(colLetter & ":" & colLetter).Select
End Function

That example function would select the entire column ( i.e. Range("A:A").Select)

Source: http://support.microsoft.com/kb/833402

I really like stackPusher's ConvertToLetter function as a solution. However, in working with it I noticed several errors occurring at very specific inputs due to some flaws in the math. For example, inputting 392 returns 'N', 418 returns 'O', 444 returns 'P', etc.

I reworked the function and the result produces the correct output for all input up to 703 (which is the first triple-letter column index, AAA).

Function ConvertToLetter2(iCol As Integer) As String
    Dim First As Integer
    Dim Second As Integer
    Dim FirstChar As String
    Dim SecondChar As String

    First = Int(iCol / 26)
    If First = iCol / 26 Then
        First = First - 1
    End If
    If First = 0 Then
        FirstChar = ""
    Else
        FirstChar = Chr(First + 64)
    End If

    Second = iCol Mod 26
    If Second = 0 Then
        SecondChar = Chr(26 + 64)
    Else
        SecondChar = Chr(Second + 64)
    End If

    ConvertToLetter2 = FirstChar & SecondChar

End Function

These answers seem strangely convoluted. Unless I'm missing something...if you want to convert numbers to letters, you can just stock them all in an array using a for loop then call on the number associated with that column letter. Like so

For intloop = 1 To 26
    colcheck(intloop) = Chr$(64 + intloop)
    For lenloop = 1 To 26
        colcheck((intloop * 26) + lenloop) = Chr$(64 + intloop) & Chr$(64 + lenloop)
        For terloop = 1 To 26
            colcheck((intloop * 676) + (lenloop * 26) + terloop) = Chr$(64 + intloop) & Chr$(64 + lenloop) & Chr$(64 + terloop)
            For qualoop = 1 To 26
                colcheck((intloop * 17576) + (lenloop * 676) + (terloop * 26) + qualoop) = Chr$(64 + intloop) & Chr$(64 + lenloop) & Chr$(64 + terloop) & Chr$(64 + qualoop)
            Next qualoop
        Next terloop
    Next lenloop
Next intloop

Then just use colcheck(yourcolumnnumberhere) and you will get the column heading associated with that letter (i.e. colcheck(703) = AAA

Haha, Lovely - let me also include my version of stackPusher's code :). We are using this functionality in C#. Works fine for all Excel ranges.:

public static String ConvertToLiteral(int number)
{
	    int firstLetter = (((number - 27) / (26 * 26))) % 26;
        int middleLetter = ((((number - 1) / 26)) % 26);

        int lastLetter = (number % 26);
        firstLetter = firstLetter == 0 ? 26 : firstLetter;
        middleLetter = middleLetter == 0 ? 26 : middleLetter;
        lastLetter = lastLetter == 0 ? 26 : lastLetter;
        String returnedString = "";
        returnedString = number > 27 * 26 ? (Convert.ToChar(firstLetter + 64).ToString()) : returnedString;
        returnedString += number > 26 ? (Convert.ToChar(middleLetter + 64).ToString()) : returnedString;
        returnedString += lastLetter >= 0 ? (Convert.ToChar(lastLetter + 64).ToString()) : returnedString;
        return returnedString;
}

Function fncToLetters(vintCol As Integer) As String
        
        Dim mstrDigits As String
        
    ' Convert a positive number n to its digit representation in base 26.
    mstrDigits = ""
    Do While vintCol > 0
        mstrDigits = Chr(((vintCol - 1) Mod 26) + 65) & mstrDigits
        vintCol = Int((vintCol - 1) / 26)
    Loop
    
    fncToLetters = mstrDigits
    
End Function

If you don't have a clue of what is the last row or column and still wanna get the Range use

LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
LastColumn = ActiveSheet.Cells(7, ActiveSheet.Columns.Count).End(xlToLeft).Column

'Column Transform number in Letter
Col_Letter = Split(Cells(1, LastColumn).Address(True, False), "$")(0)
x_range = "A1:"
y_range = Col_Letter & Trim(Str(LastRow))

'Set the range
rng_populated = x_range & "" & y_range

'Select the range
Range(rng_populated).Select