Articles

Solved excel move range of cells in VBA

November 29, 2012 at 11:50:36
Specs: Windows 7

I wish to move a range of cells to seperate wooksheet to create a table of different values. I am using it for a monte carlo simmulation.

See More: excel move range of cells in VBA

Report •


✔ Best Answer
November 30, 2012 at 09:56:04

I find it hard to believe that the Copy operation takes longer than looping through the cells. It certainly doesn't on my machine.

I ran the following code, which essentially puts the run time of each method in A1:A4. The entire macro runs in less than a second.

If I watch A1:A4 while the code is running, I can see it put the Time in each cell and, maybe I'm imagining it, but it seems that there is a slight delay between when time gets put into A3 and then into A4. That tells me that the Looping method takes ever so slightly longer than the Copy method, which is exactly what I would have expected.

Of course, as I said, since it all happens in less than a second, the difference is almost unperceivable.

Sub TimeCheck()
'Put Start time in Sheet1!A1
  Sheets("Sheet1").Cells(1, 1) = Time
'Copy Method
    Sheets("Sheet2").Range("B2:X2").Copy _
      Destination:=Sheets("Sheet1").Range("B2")
'Put End time in Sheet1!A2
  Sheets("Sheet1").Cells(2, 1) = Time
'Clear Range
    Sheets("Sheet1").Range("B2:X2").ClearContents
'Put Start time in Sheet1!A3
  Sheets("Sheet1").Cells(3, 1) = Time
'Loop through range method
    For col = 2 To 23
     Sheets("Sheet1").Cells(2, col) = Sheets("Sheet2").Cells(2, col)
    Next
'Put End time in Sheet1!A4
  Sheets("Sheet1").Cells(4, 1) = Time
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
November 29, 2012 at 17:39:31

We need a lot more information about what you are trying to do before we can offer any decent suggestions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
November 30, 2012 at 02:23:15

I tried
Sheets("sheet1").Range("B2:X2") = Sheets("sheet2").Range("B2:X2")

If the range is a single cell it works but with a range, whilst it runs no data is moved.

My main issue is speed. I currently move data cell by cell in a loop but on my machine Intel i3 processor takes 45 secondsper 1,000 iterations. As I run 50,000 iterations I would like to speed it up.


Report •

#3
November 30, 2012 at 04:44:51

Try this:

Sheets("Sheet2").Range("B2:X2").Copy _
  Destination:=Sheets("Sheet1").Range("B2")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
November 30, 2012 at 07:56:48

Thanks for this but unfortunately it takes over 50% longer as looping and doing each cell indervidually. I am looking to speed up the process. I used the code

Sheets("Summary").Range("B2:X2").Copy _
Destination:=Sheets("Montecarlo").Cells(I, 1)
Sheets("Summary").Range("B3:X3").Copy _
Destination:=Sheets("Montecarlo").Cells(I, 25)

but it incremented the row from the Summary sheet each iteration. Is this effectively to copy and paste instruction


Report •

#5
November 30, 2012 at 09:56:04
✔ Best Answer

I find it hard to believe that the Copy operation takes longer than looping through the cells. It certainly doesn't on my machine.

I ran the following code, which essentially puts the run time of each method in A1:A4. The entire macro runs in less than a second.

If I watch A1:A4 while the code is running, I can see it put the Time in each cell and, maybe I'm imagining it, but it seems that there is a slight delay between when time gets put into A3 and then into A4. That tells me that the Looping method takes ever so slightly longer than the Copy method, which is exactly what I would have expected.

Of course, as I said, since it all happens in less than a second, the difference is almost unperceivable.

Sub TimeCheck()
'Put Start time in Sheet1!A1
  Sheets("Sheet1").Cells(1, 1) = Time
'Copy Method
    Sheets("Sheet2").Range("B2:X2").Copy _
      Destination:=Sheets("Sheet1").Range("B2")
'Put End time in Sheet1!A2
  Sheets("Sheet1").Cells(2, 1) = Time
'Clear Range
    Sheets("Sheet1").Range("B2:X2").ClearContents
'Put Start time in Sheet1!A3
  Sheets("Sheet1").Cells(3, 1) = Time
'Loop through range method
    For col = 2 To 23
     Sheets("Sheet1").Cells(2, col) = Sheets("Sheet2").Cells(2, col)
    Next
'Put End time in Sheet1!A4
  Sheets("Sheet1").Cells(4, 1) = Time
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question