Speed up code

craigg3

Board Regular
Joined
Dec 23, 2002
Messages
135
Is there anyway to speed up how Im doing this? I have more code like this but only posted a portion of it. Takes about 5 seconds to run the macro, would really like to lower that time.


Code:
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = B1
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = B2
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = B3
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = B4
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = B5
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = B6
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = B7
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = B8
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = B9
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = B10
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = B11
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = B12
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = B13
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = B14
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = B15
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = L1
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = L2
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = L3
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = L4
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = L5
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = L6
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = L7
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = L8
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = L9
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = L10
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = L11
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = L12
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = L13
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = L14
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
ActiveCell.Value = L15
 
Last edited by a moderator:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,529
Can you explain in words what you are trying to do using a few examples?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,317
Office Version
365
Platform
Windows
What are B1, B2 etc. Also what is the active cell to start with?
 

craigg3

Board Regular
Joined
Dec 23, 2002
Messages
135
Sorry for not explaining more. B1, B2 etc are variables such as
B1=Sheets("Sheet1").range("a2").value
B2=Sheets("Sheet1").range("b2").value
B3=Sheets("Sheet1").range("c2").value
B4=Sheets("Sheet1").range("a3").value
B5=Sheets("Sheet1").range("b3").value
B6=Sheets("Sheet1").range("c3").value
etc.....

I have a form on a sheet that a person fills out and then when they click the submit button it is copied to another sheet.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,317
Office Version
365
Platform
Windows
You could use something like
Code:
   With ActiveCell
      .Offset(, 1).Resize(, 3).Value = Sheets("Sheet1").Range("A2:C2").Value
      .Offset(, 4).Resize(, 3).Value = Sheets("sheet1").Range("A3:C3").Value
   End With
But without knowing exactly where all the values come from & what you are trying to do, it's difficult to help much further.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,508
Office Version
2010
Platform
Windows
Sorry for not explaining more. B1, B2 etc are variables such as
B1=Sheets("Sheet1").range("a2").value
B2=Sheets("Sheet1").range("b2").value
B3=Sheets("Sheet1").range("c2").value
B4=Sheets("Sheet1").range("a3").value
B5=Sheets("Sheet1").range("b3").value
B6=Sheets("Sheet1").range("c3").value
etc.....

I have a form on a sheet that a person fills out and then when they click the submit button it is copied to another sheet.
Assuming the B1, B2, etc. that you are assigning to is on Sheet2, give this macro a try...
Code:
[table="width: 500"]
[tr]
	[td]Sub RearrangeData()
  Dim LastRow As Long, Arr As Variant
  LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
  Arr = Split(Join(Application.Transpose(Evaluate(Replace("Sheet1!A2:A#&CHAR(10)&Sheet1!B2:B#&CHAR(10)&Sheet1!C2:C#", "#", LastRow))), vbLf), vbLf)
  Sheets("[B][COLOR="#0000FF"]Sheet2[/COLOR][/B]").Range("B2").Resize(UBound(Arr) + 1) = Application.Transpose(Arr)
End Sub[/td]
[/tr]
[/table]
 

Forum statistics

Threads
1,082,305
Messages
5,364,406
Members
400,801
Latest member
julievandermeulen

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top