Is there a way to speed this up?

[andyfleisher]

I made a short macro with the recorder (and a little editing) to just cut and paste cells from a the worksheet. The basic setup is that I copied the data from a website and it is has multiple rows of data for each entry that can really be on a single line.

The pasted data looks like this. In Excel, each line comes in as a separate row.

CRN Time/Days	Course Location	Section Open/Reserved/Waitlist	Title GE Credit	Instructor Course Units
11111 8:00 - 8:50 AM, F	MGT 011A Room1120	A01 13/0/0	Elementary Accounting SS	Name 4.0

The macro takes the second row of cells and cuts/pastes it to the right of the line above. If run twice, it would look like this (including the blank cells)

CRN	Course	Section	Title	Instructor	Time/Days	Location	Open/Reserved/Waitlist	GE Credit	Course Units
11111	MGT 011A	A01	Elementary Accounting	Name	8:00 - 8:50 AM, F	Room1120	13/0/0	SS	4.0

I keep running into the annoying clipboard error that says I can't paste into a different application and added some wait time into the macro. I feel that this should run pretty quickly since it is just copying and pasting a few cells at a time and just looping over and over but it takes a while (even for just 10 iterations) and Excel seems to also hang for a but once things are done. The code is below. I have a basic input for the number of times to run the macro and that's about it.

Sub CleanClassSearchData()
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Application.ScreenUpdating = False
myNum = Application.InputBox("Enter the number of times to run the macro.")
For i = 0 To myNum
ActiveCell.Offset(1, 0).Range("A1:E1").Select
Selection.Cut
Application.Wait (Now + TimeValue("0:00:01"))
ActiveCell.Offset(-1, 5).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(2, -5).Range("A1").Select
Next i
Application.ScreenUpdating = True
End Sub

Thanks,
Andy

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, can you give us a slightly larger set of dummy sample data "Before" and "After" with XL2BB so that we can be sure we are working with the same setup you have?
(If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.)

 

[Engberg]

If the Cut/Paste and the clipboard is causing the issue, would just setting the values and then clearing the old cells work for your purpose?

 

[andyfleisher]

Thanks for the replies, I think I figured out a faster solution.

Andy

 

[jolivanes]

For people with the same or similar problem and that are looking for a solution, would you be so helpful and show them your solution.
After all you came here for help also, just like all of us and it is frustrating if you see a line of which you think that that will have an answer for you but you get nothing!!!!