Big sheet, need a way to delete fast

[Spurious]

Hello,

the following:

I got a sheet with ~15000 rows, I sort them first and then I go from the last to the first and delete if the input in column 5 is the same as in the row above, additionally I add the values in column 12.

Now, this takes forever. I takes me roughly 2.5 minutes to do it for 250 rows.

Do While i > i
    If Sheet.Cells(i, Clm) = Sheet.Cells(i - 1, Clm) Then
        Sheet.Cells(i - 1, 12) = Sheet.Cells(i - 1, 12) + Sheet.Cells(i, 12)
        Sheet.Range(Cells(i, 1), Cells(i, 25)).Delete xlShiftUp
    End If
    
    i = i - 1
Loop

Is there a faster way to do this?

Thanks in advance!

 

[Rick Rothstein]

Do While i > i

Since i will never be greater than itself, I do not see how this loop is executing at all??? Can you post your actual code? Also, explain what is going on in Column 12.

 

[Andrew Poulsom]

When I tried your code with 250 rows it took about 2 seconds. Maybe you need to set calculation to manual. By the way, this:

Do While i > i

should be:

Do While i > 1

 

[Spurious]

Since i will never be greater than itself, I do not see how this loop is executing at all??? Can you post your actual code? Also, explain what is going on in Column 12.

Sorry, simple typo in the code, I currently do it for i > 15000, so I had to manually change the code in the OP.

I got articles in column 5 and the amount sold in column 12.

When I tried your code with 250 rows it took about 2 seconds. Maybe you need to set calculation to manual.

I had them at automatic and it took forever. At manual it takes 2 seconds.

Thanks for this. What's the difference between the two and which advantage has automatic compared to manual?

It's for my boss and he is not familiar with VBA, so if it changes his excel experience, I cant really change it.

 

[Andrew Poulsom]

If calculation is automatic the workbook may be recalculated each time your code changes a cell or deletes a row. At the start of your procedure assign the calculation state to a variable and set calculation to manual. At the end of your procedure reset calculation to its previous state. That way you haven't permanently changed any of the user's settings.

 

[Spurious]

How do I do this?

And, I think my code is pretty much useless if I cant increase the speed. I currently need roughly 7 minutes for going through the data and do it what I wanted it to do. My boss needs 2 minutes if he creates a pivot table, sorts it and calculates it by hand.

My code would be faster if I had a smaller amount of rows, because he needs 2 minutes no matter what.

Is there a way to create a pivot, sort it, take out values in the data column and go from there?

Sorry for this mess.

 

[Andrew Poulsom]

Example:

Dim CalcState
CalcState = Application.Calculation
Application.Calculation = xlManual
'Your code
Application.Calculation = CalcState

 

[Rick Rothstein]

I got articles in column 5 and the amount sold in column 12.

I'm still unclear about Column 12... if it has values in it, what are you adding and putting in each row during the deletion process? Anyway, the following code will perform your deletions and I think it will do it quite quickly...

Sub RemoveDupes()
  Dim UnusedColumn As Long, LastRow As Long
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
  On Error Resume Next
  Application.ScreenUpdating = False
  With Cells(2, UnusedColumn).Resize(LastRow - 1)
    .FormulaR1C1 = "=IF(RC5=R[-1]C5,""x"","""")"
    .Value = .Value
    .SpecialCells(xlCellTypeConstants).EntireRow.Delete
  End With
  Application.ScreenUpdating = True
End Sub

 

[Andrew Poulsom]

I'm still unclear about Column 12... if it has values in it, what are you adding and putting in each row during the deletion process?

The OP is creating a summary by summing the values in column 12 for each unique item and deleting the duplicates.

 

[Spurious]

Example:

Dim CalcState
CalcState = Application.Calculation
Application.Calculation = xlManual
'Your code
Application.Calculation = CalcState

thanks

I'm still unclear about Column 12... if it has values in it, what are you adding and putting in each row during the deletion process? Anyway, the following code will perform your deletions and I think it will do it quite quickly...

Sub RemoveDupes()
  Dim UnusedColumn As Long, LastRow As Long
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
  On Error Resume Next
  Application.ScreenUpdating = False
  With Cells(2, UnusedColumn).Resize(LastRow - 1)
    .FormulaR1C1 = "=IF(RC5=R[-1]C5,""x"","""")"
    .Value = .Value
    .SpecialCells(xlCellTypeConstants).EntireRow.Delete
  End With
  Application.ScreenUpdating = True
End Sub

Not sure if this code does what I need it to do. I dont really understand it, but it seems like it deletes the entire row if there is a duplicate, but doesnt add the values in column 12.