Nothing special or fancy. Here's the issue: When I tried running this on a file with 43,352 rows and 7 columns of sums to match, I had to kill Excel after 10+ minutes because there were no results. However, when I manually checked the sums in two columns, and reduced the number of rows to 2170, the macro finished in 10 seconds. Simple match suggest that the original file should have finished in about three and one half minutes, but that didn't happen. Why?
update: I just ran this macro again on a different file with 'only' 12,046 rows. Based on the above math, this should have finished in under one minute, but it dd not finish until almost five minutes had elapsed.
Impossible to say for sure without a sample file, but if there are formulas in your workbook you may wish to consider turning off auto calculation whilst the code is running.
Thanks for the replies:
BadlySpelledBuoy - Idea sounded good, and it cut the time down to 3.5 minutes - should be about 1 minute. Helped, but not a lot.
ByteMarks - I thought that unions were basically a chunk of the spreadsheet, but in this case, individual rows need to be deleted. If my thinking is wrong, please point me to an example.
JohnTopley - File attached
OK. You show a piece of code in Post #1 that does do what you want it to, at least not in the time you think it should take.
But you do not explain what you want to achieve. I guess you want possible helpers to wade through the code and figure out what to do but you should explain in a concise manner what you want it to do.
BTW, I have not gone through the code but for some reason, this caught my eye.
If I remember right, integers are from -32768 to +32768 (you can look that up as well as I could have done). Do you exceed this? Mind you, again, I think it will be automatically converted to longs.
Someone can let us know if they feel like.
The inherent weakness of the liberal society: a too rosy view of humanity.
Thanks for the reply. In answer to your question: I simply want to remove the rows that do not add up to the values. I was doing this manually, but that can be tiresome, i.e., this macro is to support my inherent laziness.
Thanks for pointing out the Dim issue - wasn't aware of this. In this case, those two variables are well within the bounds.
I screwed up - sorry. I fixed the uploaded file - which is now a ZIP (but have no idea how to get rid of the old one). Anyway, look at columns L-R. The values in the rows should equal the values in row 1.
Re: "support my inherent laziness". You put your foot in your mouth with this.
So I'll do it for you what I asked for (explain in a concise manner) in Post #8
If the value in Range("L2") <> value in Range("L1") and
value in Range("M2") <> value in Range("M1") and
value in Range("N2") <> value in Range("N1") and
continue to column R
Then you want to do something.
Now you go one row lower but comparison is to the same row (Cells in row 1)
Then you want to do something.
Now you go one row lower but comparison is to the same row (Cells in row 1)
If this is not correct, you might have to suffer and explain.
BTW, the above should start from the bottom if deletions are to be done.
I am finding this so hard to understand...What is OP actually trying to achieve...Anyone?
@John...Your code leaves 1 line remaining... @protonLeah...Your code deletes everything...
Last edited by Sintek; 09-13-2024 at 05:22 AM.
Good Luck...
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
Also....Add a comment if you like!!!!
And remember...Mark Thread as Solved...
Excel Forum Rocks!!!
Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
Posts
30,867
Re: Why is macro so effing slow???
@Sintek: the remaining line is the "header" line.
The reason everything is deleted is because the "filtering" fails on every row. If you put 5 into cells in column O, having filtered on ALL the other columns, you will see these those rows remaining.
Your understanding not confirmed by OP?
And as the two solutions provided give different results and no confirmation from OP...One can just guess I suppose...
1. Because you do it for each object (deleting a given row) separately - you chose the wrong method of action
2. The system environment (on your computer) may be burdened with many processes running
3. Try to do this task without referring directly to the sheet, but by entering data into an array variable and performing operations on this variable (in the computer's RAM)
Try to use Dictionary and Specialcells to attempt to speed up the code. With 50,000 rows, it runs for 3 seconds
PHP Code:
Option Explicit
Sub RemoveNotAddRows()
Dim i&, j&, rng, res(), ce As Range, t
Dim dic As Object, key, begC As Range, endC As Range
Set dic = CreateObject("Scripting.dictionary")
t = Timer
For Each ce In Range("A1:AD1")
If IsNumeric(ce.Value) Then 'search the first cell with value
Set begC = ce
Exit For
End If
Next
Set endC = begC.End(xlToRight).End(xlDown) 'search last cell in row 1, then last row in last column
rng = Range(begC, endC).Value ' store data in an array
For i = 2 To UBound(rng)
dic.Add i, "" ' paste all row indexes into dictionary
Next
For j = 1 To UBound(rng, 2)
For i = 2 To UBound(rng)
If rng(i, j) <> rng(1, j) Then
If dic.exists(i) Then
dic.Remove (i) ' if at least 1 cell does not match row 1 then delete
End If
End If
Next
Next
With Range("ZZ2:ZZ" & UBound(rng)) 'use column ZZ to write results, then delete
.Value = "#N/A" 'write error sign into every rows
For Each key In dic.keys
Cells(key, "ZZ").ClearContents ' if row match row 1 then remove to be blank
Next
.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete ' delete all "#N/A" at once
.ClearContents ' delete column ZZ
End With
MsgBox Timer - t
End Sub
Last edited by bebo021999; 09-15-2024 at 09:18 AM.
3 different codes...3 different range references...3 different results...
Are you all using the same file?
Is the object to...
Compare the combined num string combinations of L1:R1 with all below...
Compare the sum of L1:R1 with all below...
Compare all nums in L1:R1 exist in below...
JohnTopley - Thanks! Learned a lot from your code, rewrote my code based on your concept, and it works. Now, need to test on something big - but I got the idea: Move the data from the sheet into an 'array'.
Bookmarks