Skip to content

Instantly share code, notes, and snippets.

@cwg999

cwg999/ArraysNotRanges.vba

Created August 3, 2017 12:14
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save cwg999/e51881588bae71295e9b7181590aa5ca to your computer and use it in GitHub Desktop.
Save cwg999/e51881588bae71295e9b7181590aa5ca to your computer and use it in GitHub Desktop.
Download ZIP
Raw
ArraysNotRanges.vba
Option Explicit
Sub WorkWithArrayExample()
Dim DataRange As Variant
Dim Irow As Long
Dim Icol As Integer
DataRange = ActiveSheet.Range("A1:A10").Value ' read all the values at once from the Excel grid, put into an array
For Irow = LBound(DataRange,1) To UBound(DataRange, 1) ' Get the number of rows.
For Icol = LBound(DataRange,2) To UBound(DataRange, 2) ' Get the number of columns.
DataRange(Irow, Icol) = DataRange(Irow, Icol) * DataRange(Irow, Icol) ' cell.value^2
Next Icol
Next Irow
ActiveSheet.Range("A1:A10").Value = DataRange ' writes all the results back to the range at once
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment