Hi All,
Just wanted to post this out there in case it could help someone out. I work alot with exports from one of our companie's application that gives out .csv files with thousands of columns. When working in VB, I noticed that my scripts were running way too slow on these files. I have one script for example that took about 210 seconds to run. I tried out just for fun to transpose the csv's content and reran the script and it now takes about 30 seconds (that's a wopping 7 times faster).
It turns out that Excel and/or VB take a much longer amount of time to find the proper cell when it's in a high number column compared a higher number row.
I knew that I had a fixed amout of rows and columns so my transpose was pretty simple :
lastCol = 3760
lastRow = WsExport.Range("a1").End(xlDown).Row
WsExport.Range("a2", WsExport.Cells(lastRow, lastCol)).Copy
WsExport.Range("a201").PasteSpecial Transpose:=True
WsExport.Rows("1:200").Delete Shift:=xlUp
Hope this helps anyone.
Regards,
Jooquase
Just wanted to post this out there in case it could help someone out. I work alot with exports from one of our companie's application that gives out .csv files with thousands of columns. When working in VB, I noticed that my scripts were running way too slow on these files. I have one script for example that took about 210 seconds to run. I tried out just for fun to transpose the csv's content and reran the script and it now takes about 30 seconds (that's a wopping 7 times faster).
It turns out that Excel and/or VB take a much longer amount of time to find the proper cell when it's in a high number column compared a higher number row.
I knew that I had a fixed amout of rows and columns so my transpose was pretty simple :
lastCol = 3760
lastRow = WsExport.Range("a1").End(xlDown).Row
WsExport.Range("a2", WsExport.Cells(lastRow, lastCol)).Copy
WsExport.Range("a201").PasteSpecial Transpose:=True
WsExport.Rows("1:200").Delete Shift:=xlUp
Hope this helps anyone.
Regards,
Jooquase
