CODEWITHSUNDEEP
excelvba
Jose M.
Jose M.

Reputation: 2340

Write shorter For Loop for Array

I wrote the following For Loop to copy and paste several ranges from one sheet to another. I used an array. The procedure works fine, but I was looking for a shorter way to do the same.

Dim copyRange(1 To 3) As String
Dim pasteRange(1 To 3) As String
Dim refRange(1 To 3) As String
Dim i As Long

copyRange(1) = "A5"
copyRange(2) = "G5"
copyRange(3) = "H5"

refRange(1) = "A"
refRange(2) = "G"
refRange(3) = "H"

pasteRange(1) = "BE3"
pasteRange(2) = "CA2"
pasteRange(3) = "CD2"

For i = 1 To 3

         refApplicantFlow.Range(copyRange(i), refApplicantFlow.Range(refRange(i) & Rows.Count).End(xlUp)).Copy _
            calcCalculations.Range(pasteRange(i))

Next i

Upvotes: 1

Views: 79

Answers (2)

Peter Albert
Peter Albert

Reputation: 17485

First of all, I would strongly recommend, to store the references to the cells as hard coded strings in your macro. The moment your workbook's structure is slightly adjusted, your macros will fail/copy the wrong things!

Instead, use named ranges. I.e. click on A5 and assign the name Source_1 or so to it. G5 will become Source_2, H5 Source_1, G5 Target_1, etc.

The use this code:

lngRows = Rows.Count
For i = 1 To 3
   Range("Target_"&i).Resize(lngRows).Value = Range("Source_"&i).Resize(lngRows).Value
Next

This way, your macro will still work, even if the workbook structure changes! And your line of code became shorter! ;-)

Upvotes: 2

Ross McConeghy
Ross McConeghy

Reputation: 874

Your macro is pretty efficient if the intent was to include a loop and arrays. The same thing could be accomplished in 3 lines:

refApplicantFlow.Range("A5", refApplicantFlow.Range("A" & Rows.Count).End(xlUp)).Copy _
calcCalculations.Range("BE3")
refApplicantFlow.Range("G5", refApplicantFlow.Range("G" & Rows.Count).End(xlUp)).Copy _
calcCalculations.Range("CA2")
refApplicantFlow.Range("H5", refApplicantFlow.Range("H" & Rows.Count).End(xlUp)).Copy _
calcCalculations.Range("CD2")

Upvotes: 0

Related Questions