I am seeking a way to optimize some of my code, and have run into a wall I can't seem to be able to tackle. Actually I am seeking an efficient way of populating a dataset with a new column of calculated data.
I have already exhausted my optimization possibilities with
So Here it goes:
This is the original code.
column_number is predetermined: this is the first empty column where the fill should take place
The function itself is not the primary question, I have many more columns added with this method. Like this:
Unfortunately, for more complicated formulae like the one above this code gets pretty slow once the dataset grows large (currently at 5000 rows and growing). I would also prefer using Application.WorksheetFunction because I don't need the formulae in the sheet.
I have been searching and reading a lot on this subject. Some claim that the For To Next cycle is not an effective way of creating new data columns. So I have been going for mass insertion, which is claimed to be pretty fast:
Please note that destws is actually the same as ThisWorkbook.Sheets("ActiveDataset") - for some reason I just needed a fully qualified reference for .Value to work.
Now as you see, the code above does not contain the For cycle, which should be good. Bad news is that it falls over row_number, which is obviously a constant - that is, it does not adjust to the proper row number.
I am in much need of a bit of insight on how to be able to create new data columns in a fast, efficient way. Thanks in advance!
I have already exhausted my optimization possibilities with
Code:
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
So Here it goes:
Code:
For row_number = 2 To num_rows
destws.Cells(row_number, column_number) = Format(destws.Range("A" & row_number), "dddd")
Next row_number
column_number is predetermined: this is the first empty column where the fill should take place
The function itself is not the primary question, I have many more columns added with this method. Like this:
Code:
destws.Cells(row_number, column_number) = 1 / Application.WorksheetFunction.CountIfs(destws.Range("C$2:C$" & num_rows), destws.Range("C" & row_number), destws.Range("D$2:D$" & num_rows), destws.Range("D" & row_number))
I have been searching and reading a lot on this subject. Some claim that the For To Next cycle is not an effective way of creating new data columns. So I have been going for mass insertion, which is claimed to be pretty fast:
Code:
ThisWorkbook.Sheets("ActiveDataset").Range(Cells(2, column_number), Cells(num_rows, column_number)).Value = Format(destws.Range("A" & row_number), "dddd")
Now as you see, the code above does not contain the For cycle, which should be good. Bad news is that it falls over row_number, which is obviously a constant - that is, it does not adjust to the proper row number.
I am in much need of a bit of insight on how to be able to create new data columns in a fast, efficient way. Thanks in advance!