Read data

[Edmund]

VBA rookie here.

How do I get VBA to “read” data of a selected range? The range is dynamic
(which can contain any number of rows or columns) & is determined by user
selection.

I heard that codes can run faster without selecting range. So instead of
having VBA to keep flipping between the source sheet (copy) & target
sheet(paste), I plan to get Excel to “memorize” the content of each row &
column, then returning it on the target sheet in one go.

I know I can do this via copy & paste method but what I’m trying to learn
here is using machine’s memory to read rows & columns.

I got the below method from a book & changed it a little. But this example
only generates reads a single column. I just don’t know how to make it read
from a dynamic range of selected cells with uncertain number of rows & column.

Pls show me. Thank you.

Private Sub Testing_ReadData ()
Dim MyArray() As Double
RowCount = Selection.Rows.Count
ReDim MyArray(RowCount)
For r = 1 To RowCount
MyArray(r) = Selection.Cells(r, 1)
Next
For Each n In MyArray
Debug.Print n
Next n
End Sub

--
Edmund
(Using Excel XP)

[Executor]

Hi Edmund,

I look at your question and made this:

Public Sub CopyRange()
Dim lRowLoop As Long
Dim lRowMax As Long
Dim lColLoop As Long
Dim lColMax As Long

Dim sSource As Worksheet
Dim sTarget As Worksheet

Dim rStart As Range

Application.ScreenUpdating = False
Set sSource = Sheets("Sheet1")

Set sTarget = Sheets("Sheet2")
sTarget.Select
Set rStart = ActiveCell

sSource.Select

lRowMax = Selection.Rows.Count
lColMax = Selection.Columns.Count

For lRowLoop = 1 To lRowMax
For lColLoop = 1 To lColMax
rStart.Offset(lRowLoop - 1, lColLoop - 1).Value =
Selection.Cells(lRowLoop, lColLoop).Value
Next
Next

Application.ScreenUpdating = True
End Sub

This solution assumes that on your target sheet the cell for the
upperleft corner where the values must be placed is allreadye selected.

HTH,

Executor


Edmund wrote:
> VBA rookie here.
>
> How do I get VBA to "read" data of a selected range? The range is dynamic
> (which can contain any number of rows or columns) & is determined by user
> selection.
>
> I heard that codes can run faster without selecting range. So instead of
> having VBA to keep flipping between the source sheet (copy) & target
> sheet(paste), I plan to get Excel to "memorize" the content of each row &
> column, then returning it on the target sheet in one go.
>
> I know I can do this via copy & paste method but what I'm trying to learn
> here is using machine's memory to read rows & columns.
>
> I got the below method from a book & changed it a little. But this example
> only generates reads a single column. I just don't know how to make it read
> from a dynamic range of selected cells with uncertain number of rows & column.
>
> Pls show me. Thank you.
>
> Private Sub Testing_ReadData ()
> Dim MyArray() As Double
> RowCount = Selection.Rows.Count
> ReDim MyArray(RowCount)
> For r = 1 To RowCount
> MyArray(r) = Selection.Cells(r, 1)
> Next
> For Each n In MyArray
> Debug.Print n
> Next n
> End Sub
>
> --
> Edmund
> (Using Excel XP)