Who can build a faster program

CW-NEN

Board Regular
Joined
Jul 18, 2007
Messages
55
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.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,077
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
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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
Joined
Jul 18, 2007
Messages
55
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
Joined
Jul 18, 2007
Messages
55

ADVERTISEMENT

firefytr, I think that will work.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,276
Office Version
  1. 365
Platform
  1. Windows
If you just want a unique list of customers why not use Advanced Filter and select the Unique Values option?
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
For a one-time operation, this will work. Either or really.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,175
Messages
5,629,170
Members
416,370
Latest member
Lgathana

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
Top