Who can build a faster program

[CW-NEN]

I have a very long spreadsheet. Each line represents a customer location. I would like a list of customers (one line for every customer). Normally I would use a pivot table, but I have to many unique entries for a pivot table to work. So what I did was create a column next to the customer name and use COUNTIF. Than I wrote a little command button.

For Each Cell In Selection
    If Cell.Value > 1 Then
        Cell.EntireRow.Delete
    End If
Next Cell

I thought that I was doing ok, until I realized that this would take a year to run. Each time a row is deleted, excel recalculates the entire column of COUNTIFs.

How do I make this faster?

Note: I had a computer glitch (lost the connection with server after pressing submit), hopefully this will not post twice. If it does please summarily delete the extra. However, it looks like the first did not go through.

 

[QuietRiot]

disable calculation?

Application.Calculation = xlCalculationManual
For Each Cell In Selection 
    If Cell.Value > 1 Then 
        Cell.EntireRow.Delete 
    End If 
Next Cell 
application.calculation = xlCalculationAutomatic

then it will only do it once at the very end

 

[Zack Barresse]

Maybe only try one delete operation?

...

Sub DeleteMyRows()
    Dim c As Range, rngUnion As Range
    For Each c In Selection
        If c.Value > 1 Then
            If rngUnion Is Nothing Then
                Set rngUnion = c
            Else
                Set rngUnion = Union(c, rngUnion)
            End If
        End If
    Next c
    If Not rngUnion Is Nothing Then
        rngUnion.EntireRow.Delete
    End If
End Sub

 

[CW-NEN]

If I do that, I think, it will delete any customer that has more than one location. The COUNTIF actually serves a purpose but, you are right, it is also the thing slowing the program down.

 

[CW-NEN]

firefytr, I think that will work.

 

[Norie]

If you just want a unique list of customers why not use Advanced Filter and select the Unique Values option?

 

[Zack Barresse]

For a one-time operation, this will work. Either or really.