# Who can build a faster program

#### CW-NEN

##### Board Regular
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.

Code:
``````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.

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### QuietRiot

##### Well-known Member
disable calculation?
Code:
``````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

##### MrExcel MVP
Maybe only try one delete operation?

...

Code:
``````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

##### Board Regular
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

##### Board Regular

firefytr, I think that will work.

#### Norie

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

#### Zack Barresse

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

Replies
6
Views
508
Replies
7
Views
273
Replies
1
Views
320
Replies
0
Views
977
Replies
26
Views
566

1,127,481
Messages
5,625,015
Members
416,065
Latest member
meiravmeron

### 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.

### Which adblocker are you using?

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

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