Delete Cells in Quickest way

[adeel1]

Hello All

i have 7,00,000 numbers in col A and in Col c i have about 85000 numbers i want to delete col C numbers from A in quickest way!

i have below code its taking to long in fact unbearable time.

Code: Select all

Sub nnenn()
Dim r As Range
p = Cells(Rows.Count, 1).End(3).Row
pp = Cells(Rows.Count, 3).End(3).Row
For i = 2 To pp
mn = Application.Match(Cells(i, "c"), Range("a2:a" & p), 0) + 1
If mn > 0 Then
If r Is Nothing Then
Set r = Cells(mn, 1)
Else
Set r = Union(Cells(mn, 1), r)
End If
End If
Next i
r.Delete Shift:=xlUp
End Sub

Adeel

[HansV]

This requires an insane number of calculations. Perhaps you should not want to do this in Excel, but in a database.

[adeel1]

you are correct that's why this code isn't working, i thought Col c data is quit long this is main cause i used this code for small data set like 4 to 5 hundred numbers its takes bit time but work with same amount of Col A,

if you are referring database such as SQL ect than i don't have access of that i have to do this in excel as i don't have any other fast medium or tool for this.

Adeel

[HansV]

Microsoft Access would do the job. I'd avoid such tasks in Excel.

[adeel1]

could you please guide me how, does still its required vba, i never use Microsoft Access in fact.

[HansV]

In Access, create a new database.
Create two tables, each with one field of type Number, with field size Long Integer or Double, depending on the data.
Let's say you name the tables tbl70000 and tbl85000, and you name the field Numbers in both tables.
Import the 700000 numbers into one table, and the 85000 numbers into the other one.
Create a Delete query like this:

S0685.png

Its SQL is

Code: Select all

DELETE tbl700000.Numbers
FROM tbl700000
WHERE (((tbl700000.Numbers) In (SELECT Numbers FROM tbl85000)));

It executed in less than a second on my PC.

[adeel1]

thnx for your help, may i have to watch some tutorial for this, thnx much

[HansV]

You'll find the sample database that I used (zipped) at DeleteDemo.zip

[adeel1]

thnx for above sample, will try

Below is just general discussion for my learning

Below code is giving me result within 2 second but this is only running in Col A with same amount of data.

Code: Select all

Sub nn()
p = Cells(Rows.Count, 1).End(3).Row
a = Range("a2:a" & p)
ReDim b(1 To UBound(a), 1 To 1)
On Error Resume Next
For i = 1 To UBound(a)
If a(i + 1, 1) - a(i, 1) >= 3 Then
k = k + 1
b(k, 1) = a(i, 1)
End If
Next i
[c2].Resize(UBound(a), 1) = b
End Sub

When i add another col as per opening post and code why code did not work.
Even that I used different approaches like match,countif,find even code did not respond.
I thought that excel build in function/formulas still fast then vba in most cases. (using these function in VBA)
I also tried two loops totally in memory start loop of 85k in this 700k loop even code doesn’t respond.
What is the main reason for such behavior?

Adeel

[HansV]

This new code performs an entirely different task than the code in your first post.

[adeel1]

yes , (i posted as sample) i posted this code that then why this is working on 700k rows and others isn't.

task is different but numbers of rows are same.

Adeel

[HansV]

The number of rows may be the same, but the second macro performs a very simple calculation: it compares each cell with the cell above it.

If you execute the line

Cells.ClearContents

it will be even faster, although a worksheet has 1048576 * 16384 = 17179869184 cells, i.e. more than 17 billion!

The original code has to match cells in two ranges; that is much more complicated.

[SpeakEasy]

task is different but numbers of rows are same.

Number of rows is the same, but number of comparisons (which is the key metric here) is not.

[adeel1]

thnx Hans sir much appreciated knowledge and help .

thnx SpeakEasy for your input too.yes you are correct.