How do I post an Array back into a new sheet?

[Coyotex3]

Good afternoon, All, trying to learn how to make better use of arrays. I was following an online video and came up with this code. The problem is, I have no idea how to paste it back into a sheet!

Sub FirstArray()
    
 Dim GL() As Variant
 Dim Dim1 As Long, Dim2 As Long

'Sheets.Add.Name = "Array"

Dim1 = Range("A6", Range("a5").End(xlDown)).Cells.Count - 1
Dim2 = Range("a5", Range("a5").End(xlToRight)).Cells.Count - 1

 ReDim GL(0 To Dim1, 0 To Dim2)
 
 For Dim1 = LBound(GL, 1) To UBound(GL, 1)
    For Dim2 = LBound(GL, 2) To UBound(GL, 2)
     GL(Dim1, Dim2) = Range("A6").Offset(Dim1, Dim2).Value
    Next Dim2
   Next Dim1
End Sub

I can see via the locals window that the array has data. How would I paste it back to a new sheet?

Thank you in advance.

 

[Marcelo Branco]

Assuming there is a sheet named Array try something like this

Sheets("Array").Activate
Range("A1").Resize(UBound(GL, 1) + 1, UBound(GL, 2) + 1) = GL

M.

 

[Coyotex3]

Assuming there is a sheet named Array try something like this

Sheets("Array").Activate
Range("A1").Resize(UBound(GL, 1) + 1, UBound(GL, 2) + 1) = GL

M.

Thank you! This is exactly what I was looking for. Question, what exactly is the Resize property and how it is being used?

This is a code that I found on YouTube when trying to follow along an array tutorial, but it kept giving me an error. Admittedly, I have no idea what this code was even trying to do to begin with.

For Dim1 = LBound(GL, 1) To UBound(GL, 1)
    For Dim2 = LBound(GL, 2) To UBound(GL, 2)
     GL(Dim1, Dim2) = Range("A6").Offset(Dim1, Dim2).Value
      ActiveCell.Offset(Dim1, Dim2).Value = GL(Dim1, Dim2)
    Next Dim2
   Next Dim1

 

[Marcelo Branco]

Question, what exactly is the Resize property and how it is being used?

Range.Resize property (Excel)

Office VBA reference topic

docs.microsoft.com

M.

 

[StephenCrump]

You can also load an Excel range directly into a Variant, rather than cell by cell as you have done. Much faster!

Dim GL As Variant

GL = Worksheets("Sheet1").Range("A1:C10").Value

GL will now be a 10 x 3 array.

(Note that lbound will always be 1, regardless of whether you're using the default Base 0, or the Option Base 1 alternative).

 

[Coyotex3]

You can also load an Excel range directly into a Variant, rather than cell by cell as you have done. Much faster!

Dim GL As Variant

GL = Worksheets("Sheet1").Range("A1:C10").Value

GL will now be a 10 x 3 array.

(Note that lbound will always be 1, regardless of whether you're using the default Base 0, or the Option Base 1 alternative).

Thank you for this. Which line would I change in my code in order to load to get this to work? Do I have to get rid of my Dim1 and Dim2 variables?

Also, if I had a dynamic range is there a way of letting Excel know to load all the date from Ranges A1 to Z10000 etc?

 

[bebo021999]

Hope it help:

Sub test()
Dim lr&, GL

' Get the last used row
lr = Range("A:Z").SpecialCells(xlCellTypeLastCell).Row

'read range value into variable array, named GL
GL = Range("A1:Z" & lr).Value

'Paste from AA100
Range("AA100").Resize(UBound(GL), UBound(GL, 2)).Value = GL

'Or overwrite from A1
'Range("A1").Resize(UBound(GL), UBound(GL, 2)).Value = GL
'Range("A1:Z" & lr).Value = GL


End Sub

 

[jolivanes]

VBA for smarties: Arrays

www.snb-vba.eu

Read, or study if you want to, explanations here.

 

[Coyotex3]

Hope it help:

Sub test()
Dim lr&, GL

' Get the last used row
lr = Range("A:Z").SpecialCells(xlCellTypeLastCell).Row

'read range value into variable array, named GL
GL = Range("A1:Z" & lr).Value

'Paste from AA100
Range("AA100").Resize(UBound(GL), UBound(GL, 2)).Value = GL

'Or overwrite from A1
'Range("A1").Resize(UBound(GL), UBound(GL, 2)).Value = GL
'Range("A1:Z" & lr).Value = GL


End Sub

Thank you very much for this!