VBA Macro programming variable sized range.

[sgrondines]

Hi Group, I'm not sure how to describe my problem so I'll do my best to explain what I'm trying to find out how to do it. I have a range of numbers which consists of anywhere from 27000 rows to over 500000 rows of data which I run a macro on that allows me to scan for duplicate numbers in rows and remove that row. Anyhow, when the macro is running it can take quite a while to run, sometimes over a day. What I would like to do is put the whole range from the spreadsheet in an array, and run the program on the array and then transpose it back to the spreadsheet. This method is suppose to be faster because of the time saved from updating the spreadsheet as it goes. I've already used the <Applications.Screenupdating> command in hopes to speed it up a bit but I'm still have long running times. How can I use VBA to find the limits of whatever range is in the spreadsheet and enter it into an array for the macro to run on it and then past the results back into the spreadsheet. I know it has something to do with using UBound and LBound but I am not sure how to configure it. Any help would be appreciated.

Thank you.

[Andrew-R]

Can't you just use Excel's deduplicate tool from the Data menu?

[jeffreybrown]

What about something like this?

Please Login or Register  to view this content.

I ran it on over 475k records and it return the results in seconds.

[HSV]

Maybe is this a solution to you @sgrondines.
http://www.excelforum.com/excel-prog...plicate-s.html

[AB33]

Jef,
You can use

Please Login or Register  to view this content.

in excel 2007, but not 2003? Am I right?

[jeffreybrown]

@AB33,

Could be, I don't have 2003 and the OP showed 2007.

[AB33]

Jef,
I can not remember exactly where I have read, or seen it, but RemoveDuplicates Columns:=1, Header:=xlYes is new 2010 only. My mind was set on this assumption. I have excel 2010 and had not tested it in 2007. Now, seeing it you use it in 2007, my assumption was wrong, hence the reason for asking.

[HSV]

You're right @AB33.

This methode is available from Excel 2007.

[jeffreybrown]

Yes, I am using 2007 and it works for 2007.

[mike7952]

Can u upload a small sample of you wb and show the layout of your worksheet. And what column/ columns to look for duplicates in?

[AB33]

Jeff and Harry!
Thank you for clarifying!

[sgrondines]

No I don't think you guys got the gist of what I was asking. I'm just looking for the code to put the data on a worksheet into an array so that it will run faster when running the macro on the array instead of the cell by cell information on the spreadsheet. The code for putting the data from the spreadsheet into the array has to be able to identify the size of the data automatically. That's why I thought that maybe UBound and LBound could be used in the code to determing the size of the data in the worksheet. Currently, the code in the macro I set up looks at each individual cell in the rows and compares them to the next cell in the row looking for a certain criteria. If it doesn't meet that criteria it deletes that row. Problem is, is that there maybe up to 500,000 rows or more of data in the worksheet and the program has to go through each row from the bottom to the top of the worksheet and delete rows that don't meet my criteria. This can take more that a day of continuous running. I heard that if the data is processed in an array that the speed of the macro may be considerable. The results from the array can then be copied back onto the worksheet.

Hope this explanation makes things clearer.

Thanks.

[mike7952]

Can you post a small sample of your workbook? And the code you are using?

[jeffreybrown]

I think we understood your question, but I think you may have missed the request in post #10 (and now #13 ).

Please include a before and after and where you want the results. A day to run the macro Please let us see what you have done so far.

[Andrew-R]

We get the gist of what you're saying, but what we're trying to tell you is that loops, be they through ranges or through arrays, are inherently slow.

If you can use Excel's native de-dupe functionality then it will be hundreds of times faster, not a few percent faster. If there's a reason that you can't just de-dupe the sheet then tell us what it is and we'll try to work around it.

[sgrondines]

I'm sorry I don't know how to explain it any better than what I just did without posting all my data and and the program which I don't feel like doing right now. I'm sorry you couldn't help.

Thanks anyhow.

[sgrondines]

PS. How do I close a thread without marking it as solved?

[jeffreybrown]

Just leave it as is...that is fine

P.S. You could just post some sample data. It doesn't have to be the whole 500,000 lines of data, just a good representation.