Efficiency Help

[vivamario]

I am writing a MAX formula to a cell, it includes 1 to 4 cells in the MAX formula.

Would it be faster to do the calculation inside vba and just print the output to the cell?

I'm doing this calculation on over 70,000 rows and I'm waiting. About an hour to go, but I have 8 more spreadsheets.

My computer right now is outputting roughly 450 cells/min.

[Jan Karel Pieterse]

The MAX function should be quite fast. Any other functions in your workbook?

[ranman256]

So you put the MAX formula in a cell, copy paste the entire sheet and it takes THIS LONG?
That should be instant.

Did you try turning off workbook calculation and do it manually after the paste?

[vivamario]

Here's the full code:


Sub ConcEdit()'
' ConcEdit Macro
' Build Conc Edit Column
'
' Keyboard Shortcut: Ctrl+q
'
Application.ActiveWorkbook.ActiveSheet.Select
Dim strRange As String
Dim strDate As String
strDate = x
Dim i, intStart, intFinish As Integer
i = 1
'Set the initial cell of the range
intStart = i
'Loop until you hit a date and stop
Do Until IsEmpty(Range("D1").Offset(i, 0))
strDate = Range("C1").Offset(i, 0).Value
Range("E1").Offset(i, 0).Value = strDate
Do Until Not strDate = ""
i = i + 1
strDate = Range("C1").Offset(i, 0).Value
Loop
'Set the final cell of the range
intFinish = i
'Fill out Conc Edit
For i = intStart To intFinish
Range("E1").Offset(i, 0).Formula = "=MAX(D" & _
intStart + 1 & ":D" & intFinish + 1 & ")"
Next
intStart = intFinish + 1
Loop

End Sub

Also when the code gets to line 32,767 or close to that. I have to restart it because the integer type goes out of range and I change to double.

[ashleyuk1984]

Regarding the Integer problem... Change Integer to Long

[yoslick11]

Op, can you attach one of your sheets to this thread? Also, change the declare line to



Dim i As Long, intStart As Long, intFinish As Long


or else you are delaring the first 2 variables as variants. What is the variable x referring when initializing strDate?

[vivamario]

Updated the variables and I deleted that line of code with 'strDate = x'. That was leftover from an idea I had.

Sorry, I'm nervous to link anything because the project is confidential. Here's a picture with what I'm doing. I have formulas where I used formulas so you can see what they are. The MAX formula applies to the column to the left. And the IF statement is relative all the way down the C column.

11826

[Paul_Hossler]

You'd be better off sanitizing a small file and posting it with the BEFORE and the AFTER along with a description of the logic

[vivamario]

11830
I paste in column A, B, and D. These columns are all sorted in alphabetical order by column A.

The logic is already in column C that eliminates any repeat dates from appearing.

My macro builds column E, which finds the max in column D of like dates. If there is only one row with a like date, it outputs the max of just that one cell.

My problem is the way I do it takes a long time for a computer to process. Is there a more efficient way to do it?

[Paul_Hossler]

Is there a more efficient way to do it?


Probably, but a small sample actual real true Excel workbook (not pictures) with sanitized data would eliminate the need to people to re-create the wheel to assist

[vivamario]

Ok, here it is.

Something I've noticed, the row to row speed of my macro is a function of how many total rows I have. I found that interesting because I don't know why that would be the case.

I was getting about 200 rows/min with 100,000 rows compared to what I was getting before. To run the macro with the 300 rows in the spreadsheet took but a second.

I use the box on the right to check to make sure I included all the wells. If there is an error, there will be not a 1 somewhere and I fix it so it's all ones.

Basically I need to take the max of repeat dates.

[p45cal]

Is this the sort of thing you're looking for:?
11838
If so, it took about 20 seconds to create a pivot table then no noticeable time to get the data.
If you answer yes, I'll give more details, automating the process if you want.

ps. …and you won't need column C.

[vivamario]

I figured out how to recreate what you did. Yes, the data is organized beautifully. It is significantly faster and gets me where I want to be.

I don't want to waste too much more of your time, but could you throw down the first couple lines of code to make a Pivot Table programmatically. It would get me started a lot faster.

[p45cal]

Sub blah()
With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Template!R1C1:R390C4", Version:=xlPivotTableVersion14).CreatePivotTable(TableDestination:="Template!R6C15", DefaultVersion:=xlPivotTableVersion14)
With .PivotFields("Well")
.Orientation = xlRowField
.Position = 1
End With
With .PivotFields("Date")
.Orientation = xlRowField
.Position = 2
End With
.AddDataField .PivotFields("Conc"), "Max", xlMax
.RowAxisLayout xlTabularRow
.PivotFields("Well").LayoutBlankLine = True
.PivotFields("Date").LayoutBlankLine = True
'.PivotFields("Date Edit").LayoutBlankLine = True
.PivotFields("Conc").LayoutBlankLine = True
.ColumnGrand = False
.RowGrand = False
For Each pf In .PivotFields
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next pf
End With
End Sub
This is using Excel 2010, if you're using an earlier version a tweak may need to be made to instances of xlPivotTableVersion14.
This uses your data as in your file, since you won't need column C you'll need some changes for that too.


pps. …which can be pared down:
Sub blah3()
With ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Template!R1C1:R390C4").CreatePivotTable(TableDestination:="Template!R6C15")
With .PivotFields("Well"): .Orientation = xlRowField: .LayoutBlankLine = True: .Subtotals(1) = True: .Subtotals(1) = False: End With
.PivotFields("Date").Orientation = xlRowField
.AddDataField .PivotFields("Conc"), "Max", xlMax
.RowAxisLayout xlTabularRow
.ColumnGrand = False
End With
End Sub

[vivamario]

This is perfect. Thank you again.