Small self post

It surprises me how far my VBA skills have advanced. There’s still so much that I don’t know and for the things I’m doing, I’ll probably never learn about. But I’ve learned some things that have become invaluable to executing fast code.

 

I’ve learned that for whatever reason, VBA isn’t a fan of the cut past option, regardless of the cutcopymode=false line. I’m not entirely sure what the line means, but I’ve heard that it’s suppose to clear the memory of excel (as far as it’s cut/copy memory is concerned). This makes sense as once you’ve cut something, you no longer need excel to store it since it’s in the place where it needs to go. However, when running code that is as lengthy as mine is (10s of thousands of pieces of data), the code starts lagging, freezing up, and sometimes crashing the program. The workaround for this is that excel has no problem ¬†inserting data from another cell.

In other words, excel does not like:

cut A1 and place it in A2

But there’s no issue with

make A2=A1, then delete A1

The second piece of code runs MUCH MUCH faster (less than 2 seconds whereas my cut paste code was taking me as long as 5-10 minutes to run with the chance to crash). Additionally, bulk deleting makes it so you often don’t have to run the second part of the second code until all the values have been inserting.

The following code I was using to shift data. the before and after. I had data stored in 7 columns that ran down (columns A-G, rows 1-x. I call this vertical data) I wanted the data to be “pushed” in a sense, to be formatted in 7 ROWS, running to the right (columns A-x, rows 1-7, or horizontal data).

Before:

Sub datapush()
‘second part containing max/min/high/low
Cells(1, 1).Select
executionnumbers = ActiveCell.End(xlDown).Row
For x = 1 To executionnumbers
Range(Cells((x + 1), 1), Cells((x + 1), 7)).Select
Selection.Cut
Range(“I1: N1”).Select
ActiveSheet.Paste
Application.CutCopyMode = False

‘for max
Cells(1, 9).Select
Selection.Cut
Cells(3, (x + 8)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
‘for min
Cells(1, 10).Select
Selection.Cut
Cells(4, (x + 8)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
‘for High
Cells(1, 11).Select
Selection.Cut
Cells(5, (x + 8)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
‘for Low
Cells(1, 12).Select
Selection.Cut
Cells(6, (x + 8)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
‘for size
Cells(1, 13).Select
Selection.Cut
Cells(7, (x + 8)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
‘for first
Cells(1, 14).Select
Selection.Cut
Cells(8, (x + 8)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
‘for same/difference
Cells(1, 15).Select
Selection.Cut
Cells(9, (x + 8)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next x
End Sub

 

 

After:

Sub smarterdatapush()
‘second part containing max/min/high/low
‘places cells starting with the I1 cell
Cells(1, 1).Select
executionnumbers = ActiveCell.End(xlDown).Row
executionrows = ActiveCell.End(xlToRight).Column
‘for how many columns of data there are
For y = 1 To executionrows
‘for how many rows of data there are
For x = 1 To executionnumbers
‘where to place data
Cells(y, 8 + x) = Cells(x, y)
Next x
Next y
End Sub

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s