transpose 250K rows then AutoFill 250K/3 rows bogs down excel

P

pavanb

New Member
Joined
Jun 9, 2015
Messages
5
Hi There

I appreciate your insight on this.

Problem starts at step 1. Then 2. …so on, excerpts below. Is a pivot table more efficient than transpose? What about the rest of the code? It keeps getting slower and never finishes in 1 hr. Task manager shows excel pegged at 13% cpu and 3.25GB memory (read somewhere that ~3gb is VBA’s limit) . I have office 365 64 bit, 100 GB SSD & 20 GB memory . win 7 64 bit.

Manual, no vba, pivot and charting works. Thank you.

'transpose sets of 3 on a 250K row dataset. got the following from this forum
ActiveCell.Formula = "=INDEX('Power Data Raw'!$B$2:$B$258898,ROWS(B$2:B2)*3-3+COLUMNS($B2:B2))"

' the following works on 20 cells
'Selection.AutoFill Destination:=Range("B2:D22")...doesn't bog down

'1. The following works and takes 2 mins
Selection.AutoFill Destination:=Range("B2:D86291"), Type:=xlFillDefault

' 2. The following takes 10 mins
'Change all the cells in a range to values to make it efficient.
With Range("B:D")
.Value = .Value
End With







Complete code below and dataset is at,
https://www.dropbox.com/sh/d57qcdkx6087u72/AACv4MqXDmpVrxN-Qci0Pjida?dl=0

Sub Macro2()
'
' Macro2 Macro
'

'delete raw columns
Range("A:A,C:H,J:AF,AH:AQ").Select
Range("AJ1").Activate
Selection.Delete Shift:=xlToLeft

' xlsb binary saves space
ActiveWorkbook.SaveAs FileFormat:=xlExcel12, CreateBackup:=False

' name sheet Power data Raw
Sheets(ActiveSheet.Name).Select
Sheets(ActiveSheet.Name).Name = "Power Data Raw"


'Fit
Cells.Select
Cells.EntireColumn.AutoFit

'name
Sheets.Add After:=ActiveSheet
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Total"

'transpose real power - temporary column
Range("B2").Select
'F8 = B2 delete me I is B, 8 is 2, $I$258904 is $B$258898, H8 is D2
'for 4 th generator make the following 4.
ActiveCell.Formula = "=INDEX('Power Data Raw'!$B$2:$B$258898,ROWS(B$2:B2)*3-3+COLUMNS($B2:B2))"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:D2"), Type:=xlFillDefault
Range("B2:D2").Select
'Selection.AutoFill Destination:=Range("B2:D22")...doesn't bog down
Selection.AutoFill Destination:=Range("B2:D86291"), Type:=xlFillDefault

'Change all the cells in a range to values
With Range("B:D")
.Value = .Value
End With

' B8 = A2
Range("A2").Select
' this is set for 4 generators
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[4])"

Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A86291"), Type:=xlFillDefault

With Range("A:A")
.Value = .Value
End With

'delte temporary col
Columns("B:D").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft



'transpose reactive power - temporary column
Range("B2").Select
'for 4 th generator make the following 4.
ActiveCell.Formula = "=INDEX('Power Data Raw'!$C$2:$C$258898,ROWS(B$2:B2)*3-3+COLUMNS($B2:B2))"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:D2"), Type:=xlFillDefault
Range("B2:D2").Select
'Selection.AutoFill Destination:=Range("B2:D22")...doesn't bog down
Selection.AutoFill Destination:=Range("B2:D86291"), Type:=xlFillDefault

'Change all the cells in a range to values
With Range("B:D")
.Value = .Value
End With

'this is different from above
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("B2").Select
' this is set for 4 generators
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[4])"

Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B86291"), Type:=xlFillDefault

With Range("B:B")
.Value = .Value
End With

'delte temporary col
Columns("C:E").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft


' time
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Formula = "=INDEX('Power Data Raw'!$A$2:$A$258898,ROWS(A$2:A2)*3-3+COLUMNS($A2:A2))"
Range("A2").Select
Selection.AutoFill Destination:=Range("A2:A86291"), Type:=xlFillDefault
Range("A2").Select

Sheets("Power Data Raw").Select
ActiveWindow.SelectedSheets.Delete

ActiveWorkbook.Save

End Sub
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
I once had to deal with a project that included filling in complex UDF formulas into a dataset that could stretch down in to the 200k rows range, or more. When I ran the macro on 100 rows, it took seconds, on 1000 rows, it took noticeably longer than a minute, well more than "10 times longer". And at 5000 rows the exponential time lag was ridiculous.

Rather than being overly bright trying to discover and kill all the reasons causing the exponential time growth when run against such large data sets, I took what I know to be true, 100 rows was fast!

So I finished my macro for inserting formulas into a row, then filling down 100 rows, then removing the formulas and leaving the values behind.

Then I slipped that into a LOOP that did my entire data set 100 rows at a time. What once crashed after an hour+ of waiting now finished with no errors in about 10 minutes. Perhaps this approach will serve you.
 
Upvote 0
Hi..

Try this code.. it got the result in less than a second..

To test:

* I saved your csv file as xlsm
* Then ran your "Delete Raw Columns" code.
* Then.. added a new sheet called "Result"

* Then.. ran the following code (assigned to an active X button) from the sheet with all the data on it..

Result written to "Result" sheet..

Code: 
Private Sub CommandButton1_Click()
    Dim x, y, i As Long, cnt As Long
    With [A1].CurrentRegion
        x = .Offset(1, 1).Resize(.Rows.Count - 1, 1).Value: cnt = 1
        ReDim y(1 To (.Rows.Count - 1) / 3, 1 To 3)
        For i = 1 To UBound(x) Step 3
            y(cnt, 1) = x(i, 1): y(cnt, 2) = x(i + 1, 1): y(cnt, 3) = x(i + 2, 1)
            cnt = cnt + 1
        Next i
        Sheets("Result").[B2].Resize(UBound(y), 3).Value = y
    End With
End Sub
 
Upvote 0
Apo,

It was lightning fast. Unfortunately I don't understand your code yet and my group need the result in 24 hrs or I will be doing things manually :banghead:. To cover myself with pivots, I am keeping the first column. Changing things to,
'delete raw columns - not deleting A
Range("C:H,J:AF,AH:AQ").Select

I appreciate your help. Thanks.
 
Upvote 0
Hi..

On my way to work.. if i don't get a chance there.. I will look in 8.5 hours time..
 
Upvote 0
Hi..

I must admit.. I am not sure exactly how you want the output to look...

This changed code produces output as shown below (first 20 rows shown only)..

If this is not the out put you want.. upload a small sample workbook showing the exact output layout needed for the first 20 rows (based on your original data)..

Code: 
Private Sub CommandButton1_Click()
    Dim x, y, i As Long, cnt As Long
    
    'delete raw columns
Range("C:H,J:AF,AH:AQ").Select
Range("AJ1").Activate
Selection.Delete Shift:=xlToLeft


    With [A1].CurrentRegion
        x = .Offset(1).Resize(.Rows.Count - 1, 3).Value: cnt = 1
        ReDim y(1 To (.Rows.Count - 1) / 3, 1 To 4)
        For i = 1 To UBound(x) Step 3
            y(cnt, 1) = x(i, 1): y(cnt, 2) = x(i, 3): y(cnt, 3) = x(i + 1, 3): y(cnt, 4) = x(i + 2, 3)
            cnt = cnt + 1
        Next i
        Sheets("Sheet1").[B2].Resize(UBound(y), 4).Value = y
    End With
End Sub
Excel 2012
BCDE
192.168.100.1
192.168.100.1
192.168.100.1
192.168.100.1
192.168.100.1
192.168.100.1
192.168.100.1
192.168.100.1
192.168.100.1
192.168.100.1
192.168.100.1
192.168.100.1
192.168.100.1
192.168.100.1
192.168.100.1
192.168.100.1
192.168.100.1
192.168.100.1
192.168.100.1

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"]429866[/TD]
[TD="align: right"]408332[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]430520[/TD]
[TD="align: right"]402807[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]439233[/TD]
[TD="align: right"]401198[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]442443[/TD]
[TD="align: right"]404546[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]434462[/TD]
[TD="align: right"]412974[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]430979[/TD]
[TD="align: right"]404633[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]430354[/TD]
[TD="align: right"]401130[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]431750[/TD]
[TD="align: right"]400776[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]431763[/TD]
[TD="align: right"]401551[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]430721[/TD]
[TD="align: right"]401803[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]428789[/TD]
[TD="align: right"]400372[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]432686[/TD]
[TD="align: right"]404403[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]439812[/TD]
[TD="align: right"]412816[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]435304[/TD]
[TD="align: right"]410909[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]430231[/TD]
[TD="align: right"]401902[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]442308[/TD]
[TD="align: right"]404463[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]443562[/TD]
[TD="align: right"]411100[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]431603[/TD]
[TD="align: right"]412287[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]428855[/TD]
[TD="align: right"]401930[/TD]
[TD="align: right"]0[/TD]

</tbody>
Sheet1​
 
Last edited:
Upvote 0
Dear Apo,

We need to discard column B (it was really for pivot use, plan b) and Sum column C to E. Also the time column needs to used and few more steps below.

In the dropbox location I have upload the xlsb (where I am at today) and the macro I used is at the end here (only 22 rows operated on for speed). In the worksheet “total”, I need to plot Col B & G against time (col A).

I have 15 .xlsb’s (days) like this and I need to put it on one chart.

Therefore, my next step needs to be to decimate the rows by say 10 ( a user specified variable/cell) and have the Avg, Minimum, Maximum of decimation and plot it.

Sincerely appreciate your help and enthusiasm on this.

Best,
Pavan

Sub PowerOnly()
' Works on the csv file
' not optimized 2015 06 10


'save state
'Get current state of various Excel settings; put this at the beginning of your code

screenUpdateState = Application.ScreenUpdating

statusBarState = Application.DisplayStatusBar

calcState = Application.Calculation

eventsState = Application.EnableEvents

displayPageBreakState = ActiveSheet.DisplayPageBreaks 'note this is a sheet-level setting

'turn off some Excel functionality so your code runs faster

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.Calculation = xlCalculationManual

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting

'>>your code goes here<<


'delete raw columns
Range("C:H,J:AF,AH:AQ").Select
Range("AJ1").Activate
Selection.Delete Shift:=xlToLeft

'remove 192.168.100.
Columns("A:A").Select
Selection.Replace What:="192.168.100.", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False


'don't loose ms precision on time
Columns("B:B").Select
Selection.NumberFormat = "hh:mm:ss.0"


' **** add suffix "Power only" before.xlsb
ActiveWorkbook.SaveAs FileFormat:=xlExcel12, CreateBackup:=False

' name sheet Power data Raw
Sheets(ActiveSheet.Name).Select
Sheets(ActiveSheet.Name).Name = "Power Data Raw"


'Fit
Cells.Select
Cells.EntireColumn.AutoFit

'name
Sheets.Add After:=ActiveSheet
'Sheets("Sheet1").Select
Sheets(ActiveSheet.Name).Name = "Total"

'transpose real power - temporary column
Range("B2").Select
'F8 = B2 delete me I is B, 8 is 2, $I$258904 is $B$258898, H8 is D2
'for 4 th generator make the following 4.
ActiveCell.Formula = "=INDEX('Power Data Raw'!$C$2:$C$258898,ROWS(B$2:B2)*3-3+COLUMNS($B2:B2))"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:D2"), Type:=xlFillDefault
Range("B2:D2").Select
'Selection.AutoFill Destination:=Range("B2:D22")...doesn't bog down
'Selection.AutoFill Destination:=Range("B2:D86291"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("B2:D22"), Type:=xlFillDefault

' 'Change all the cells in a range to values
' With Range("B:D")
' .Value = .Value
' End With
'stopped here 2015 06 12

' Sum
'B8 = A2
Range("A2").Select
' this is set for 4 generators
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[4])"

Range("A2").Select
'Selection.AutoFill Destination:=Range("A2:A86291"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("A2:A22"), Type:=xlFillDefault

'slow*******
' With Range("A:A")
' .Value = .Value
' End With

'delte temporary col
' Columns("B:D").Select
' Application.CutCopyMode = False
' Selection.Delete Shift:=xlToLeft

Range("C1").Select
ActiveCell.FormulaR1C1 = "Can delete these 3 colums"
Range("C1:E1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With



'transpose reactive power - temporary column
Range("G2").Select
'for 4 th generator make the following 4.
ActiveCell.Formula = "=INDEX('Power Data Raw'!$D$2:$D$258898,ROWS(G$2:G2)*3-3+COLUMNS($G2:G2))"
Range("G2").Select
Selection.AutoFill Destination:=Range("G2:I2"), Type:=xlFillDefault
Range("G2:I2").Select
'Selection.AutoFill Destination:=Range("G2:I22")...doesn't bog down
'Selection.AutoFill Destination:=Range("G2:I86291"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("G2:I22"), Type:=xlFillDefault

Range("H1").Select
ActiveCell.FormulaR1C1 = "Can delete these 3 colums"
Range("H1:J1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With


'Change all the cells in a range to values
'slow*******
' With Range("B:D")
' .Value = .Value
' End With

'this is different from above
' Columns("B:B").Select
' Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Range("F2").Select
' this is set for 4 generators
ActiveCell.FormulaR1C1 = "=SUM(RC[1]:RC[4])"

Range("F2").Select
Selection.AutoFill Destination:=Range("F2:F22"), Type:=xlFillDefault

' With Range("B:B")
' .Value = .Value
' End With

' 'delte temporary col
' Columns("C:E").Select
' Application.CutCopyMode = False
' Selection.Delete Shift:=xlToLeft


' time
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2").Select
ActiveCell.Formula = "=INDEX('Power Data Raw'!$B$2:$B$258898,ROWS(A$2:A2)*3-3+COLUMNS($A2:A2))"
Range("A2").Select
'Selection.AutoFill Destination:=Range("A2:A86291"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("A2:A22"), Type:=xlFillDefault
Range("A2").Select

' Sheets("Power Data Raw").Select
' ActiveWindow.SelectedSheets.Delete


'don't loose ms precision on time
Columns("A:A").Select
Selection.NumberFormat = "hh:mm:ss.0"

'after your code runs, restore state; put this at the end of your code

Application.ScreenUpdating = screenUpdateState
Application.DisplayStatusBar = statusBarState
Application.Calculation = calcState
Application.EnableEvents = eventsState
ActiveSheet.DisplayPageBreaks = displayPageBreaksState 'note this is a sheet-level setting


ActiveWorkbook.Save

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

Sean15
  • Question Question
Could you help improve recorded macro
2
Replies
13
Views
410
Sean15
Sean15
S
  • Solved
VBA autofilling code
Replies
6
Views
441
Peter_SSs
Peter_SSs
T
  • Solved
Autofill formula via VBA erroring
Replies
3
Views
330
Alex Blakenburg
A
J
  • Question Question
VBA removing heading in spreadsheet
Replies
3
Views
239
rlv01
R
P
  • Question Question
Help with Vba Code
Replies
3
Views
433
Coyotex3
C

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top