2 Dimensional array from range [closed]

by

Assuming your spreadsheet looks kind of like this

spreadsheet

There is a really easy way to stick that in a 2D array

Dim arr as Variant
arr = Range("B6:H14").Value

The easiest way to print this array back to spreadsheet 

Sub PrintVariantArr()

    Dim arr As Variant
    arr = Range("B6:H14")

    Range("B16").Resize(UBound(arr, 1), UBound(arr, 2)) = arr

End Sub

Or you can iterate/loop the array 

Sub RangeToArray()

    Dim arr As Variant
    arr = Range("B6:H14").Value
    Dim r As Long, c As Long

    r = 16
    c = 2

    Dim i, j
    For i = LBound(arr, 1) To UBound(arr, 1)
        For j = LBound(arr, 2) To UBound(arr, 2)
            Cells(r, c) = arr(i, j)
            c = c + 1
        Next j
        c = 2
        r = r + 1
    Next i

End Sub

And your array printed back to the spreadsheet

result

More Related Contents:

  1. Print function selecting wrong Type of Change option excel vba
  2. What is the difference between .text, .value, and .value2?
  3. Excel VBA – How to add dynamic array formula
  4. How can I concatenate strings in VBA?
  5. Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table
  6. Loading addins when Excel is instantiated programmatically
  7. Excel’s fullname property with OneDrive
  8. Trying to use variables as values vba to sql
  9. Copy Excel range as Picture to Outlook
  10. VBA (Excel) Dictionary on Mac?
  11. Return RGB values from Range.Interior.Color (or any other Color Property)
  12. Refresh Excel VBA Function Results
  13. excel VBA run macro automatically whenever a cell is changed
  14. Code for looping through all excel files in a specified folder, and pulling data from specific cells
  15. Translate text using vba
  16. Concatenate column headers if value in rows below is non-blank
  17. Update excel sheet based on outlook mail [closed]
  18. Copy sheet and get resulting sheet object?
  19. Sending mail using Outlook where the Send method fails
  20. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  21. Using a variable for row in (row,column) for a range in VBA
  22. Unicode string literals in VBA
  23. How to get the path of current worksheet in VBA?
  24. Excel VBA to match and line up rows
  25. Create a new object using the text name of the class
  26. How to open a workbook specifying its path
  27. Delete all data rows from an Excel table (apart from the first)
  28. Excel Hide/Show all tabs on Ribbon except custom tab
  29. Macros Not Showing Up in Macro Table
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment