EXCEL VBA QUESTION - Ranges, Variant data types RRS feed

  • Question

  • So I'm looking at this section of code and trying to improve it

    Dim sourceRange as Range
    Dim sourceRowRangeVariant As Variant
    Dim transposedVariant As Variant
    Dim lngRows As Long
    Set sourceRange = Range("Tests")
    sourceRowRangeVariant = sourceRange.Value
    transposedVariant = Application.Transpose(sourceRowRangeVariant)

    From this link I hear Variant data types aren't that efficient

    Is there an alternative?

    I can't wrap my head around what my friend has done in the last two lines? 

    Any help will be appreciated


    • Moved by Neda Zhang Monday, November 28, 2016 7:24 AM Moved from VB forum
    Sunday, November 27, 2016 4:07 PM

All replies

  • For VBA try the Visual Basic for Applications (VBA) Forum.

    La vida loca

    • Proposed as answer by IronRazerz Sunday, November 27, 2016 4:16 PM
    Sunday, November 27, 2016 4:10 PM
  • Hi VBNovice01,

    Thank you for posting here.

    Since your issue is more related to Excel. I will move this thread to Excel for Developers for better.

    Please refer:

    Thanks for your understanding and support.

    Best Regards,

    Neda Zhang

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Monday, November 28, 2016 7:23 AM
  • Re:  using Variants with Excel

    Code that makes a Variant equal to a Range creates an Array containing the cell values in the range.
    It is a very useful technique that allows you to loop thru the array instead of looping thru the individual cells.
    You can alter the array and then place it back anywhere in the sheet.  So...

      Dim rngSource as Range
      Dim arrSource as Variant
      Set rngSource = Worksheets("Sludge").Range("B2:E10")
      arrSource = rngSource.Value

    Or just...
      arrSource = Worksheets("Sludge").Range("B2:E10").Value

    The above creates an array containing the cell values
    The array will have nine rows and 4 columns and is "1" based.  
    It is the same as doing this...

      Dim rngSource as Range
      Dim arrSource() as Variant
      Set rngSource = Worksheets("Sludge").Range("B2:E10")
      ReDim arrSource(1 to 9, 1 to 4)
      Dim i as Long
      Dim j as Long

      For i = 1 to 9
       For j = 1 to 4
        arrSource(i, j) = rngSource(i, j).Value

    Jim Cone
    Portland, Oregon USA (Dropbox)
    (free & commercial excel add-ins & workbooks)
    Monday, November 28, 2016 3:27 PM