Create excel ranges using column numbers in vba

Question

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

User · Answer

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

enter image description here

User · Answer

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  gt  27   26    Convert ToChar firstLetter   64  ToString      returnedString          returnedString    number  gt  26    Convert ToChar middleLetter   64  ToString      returnedString          returnedString    lastLetter  gt   0    Convert ToChar lastLetter   64  ToString      returnedString          return returnedString

User · Answer

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  gt  0 Then         ConvertToLetter   Chr iAlpha   64      End If     If iRemainder  gt  0 Then         ConvertToLetter   ConvertToLetter  amp  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  amp       amp  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

User · Answer

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  gt  0         mstrDigits   Chr   vintCol - 1  Mod 26    65   amp  mstrDigits         vintCol   Int  vintCol - 1    26      Loop      fncToLetters   mstrDigits  End Function

User · Answer

Below are two solutions to select the range A1  Cells 1 1  Select   row 1  column 1   Range  quot A1 quot   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  --   quot Cells quot  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  quot hard coded quot  representations - not dynamic  Thanks to Kim Gysen

User · Answer

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  gt  25         Rtn   Chr 65    Value Mod 26    amp  Rtn         Value   Fix Value   26  - 1     Wend     Rtn   Chr 65   Value   amp  Rtn     ColumnNumToStr   Rtn End Function

User · Answer

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   amp  Chr  64   lenloop          For terloop   1 To 26             colcheck  intloop   676     lenloop   26    terloop    Chr  64   intloop   amp  Chr  64   lenloop   amp  Chr  64   terloop              For qualoop   1 To 26                 colcheck  intloop   17576     lenloop   676     terloop   26    qualoop    Chr  64   intloop   amp  Chr  64   lenloop   amp  Chr  64   terloop   amp  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

User · Answer

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  amp  SecondChar  End Function

User · Answer

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

[excel] Create excel ranges using column numbers in vba?

Examples related to excel

Examples related to vba