Excel Howto?
Excel VBA how to?

-Aviral Mittal




1. How to get all the values from a Colum into a variable?
At first instance, its intuitive to think that if all the values in a column are desired, a vector or array will be needed to store it.
Its correct however not necessary. A simple string can be used to store all of the values separated by a comma.
But it is very important, that the comma character does not appear into the column values itself.
Here a function is presented which will get the values from all rows of a column in a comma separated string.
Actually the string separater is an optional input value to this function. So if any other comma separater is desired, the function will co-op
Start Row number is also an optional input. Just in case....

'------------------------
Function getColValues(ColNo As Long, Optional shtName As String, Optional strSeparater As String, Optional stRow As Long) As String
'This function will return a comma separated value of all entries in a column.
'The default string separter is a comma, however it can be overrieden by passing it as a input while using the function.
'Example use 1: MsgBox getColValues(ColNo:=2, shtName:="Sheet1", strSeparater:="#")
'Example use 2: MsgBox getColValues(ColNo:=2, shtName:="Sheet1", strSeparater:="#", stRow:=3)
'Example use 3: MsgBox getColValues(ColNo:=2)
Dim sht As Worksheet
Dim lRow As Long 'Last used row in the Column
Dim ii As Long
  If (shtName = "") Then
    shtName = "Sheet1"
  End If
  If (strSeparater = "") Then
    strSeparater = ","
  End If
  If (stRow = 0) Then
    stRow = 1
  End If
  On Error GoTo errorHandler
  Set sht = ThisWorkbook.Worksheets(shtName) 'Test and tank if sht isn't there.
  On Error GoTo 0 'Nullify go to errorHandler
  If ColNo = 0 Then
    Debug.Print "From Function getColValues: ColNo has illegal value of 0"
    Exit Function
  End If
 
  With Sheets(shtName)
    lRow = .Cells(.Rows.Count, ColNo).End(xlUp).Row 'Evaluate the last used row
    For ii = stRow To lRow
      If (ii = stRow) Then
        getColValues = .Cells(ii, ColNo).Value
      Else
        getColValues = getColValues & strSeparater & .Cells(ii, ColNo).Value
      End If
    Next ii
  End With
  Exit Function
errorHandler:
  Debug.Print "ERROR! From Function 'getColValues': Sheet " & "'" & shtName & "'" & " Does not exist"
  MsgBox "ERROR! From Function 'getColValues': Sheet " & "'" & shtName & "'" & " Does not exist"
End Function
Sub try_getColValues()
  MsgBox getColValues(ColNo:=2, shtName:="Sheet1", strSeparater:="#")
  MsgBox getColValues(ColNo:=2, shtName:="Sheet1", strSeparater:="#", stRow:=3)
End Sub

Download the WB with the above code:
Book6.xlsm


<- Previous                                                                                            Next ->

Key Words: Excel VBA: Get column values in a varaible.