Yeah, back about 100 years ago (ok, maybe that's exaggerating)
I discovered that when moving top-to-bottom and delete a row,
it makes the "next" row the "current" row, then the next loop iteration
caused it to skip that row.
You handled that by reducing the loop counter.
But I decided that working bottom-up would eliminate the problem.
I just had to figure out how VBA loops handled decreasing steps.
very observant of you recognize the value!
(I've had people "correct" my step counter and complain because it doesn't work.
I mean after all: "Excel should recognize that if you're going from 10 to 1
it's going "down"! "... yeah.. that would be nice, but it doesn't)
good luck!
Paul
>
>From: Dave Bonallack <davebonall...@hotmail.com>
>To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com>
>Sent: Fri, October 1, 2010 10:00:46 AM
>Subject: RE: $$Excel-Macros$$ Macro hangs
>
>Hi Paul,
>This code is excellent! I've looked up the Help for the Dictionary object,
>which
>clarified it's use quite well. At first I wondered why you had decided to work
>backwards, up through the code; then realised (I think) that it's so you can
>delete a row (Delete Shift:=xlUp) without having to account for the lost row
>number as you would if you were working down the data. Very cool! Wish I'd
>thought of that.
>Thanks also for the notations, which really helped me understand your process.
>And thanks for your time.
>Regards - Dave.
>
>> Date: Wed, 29 Sep 2010 05:15:54 -0700
>> From: schreiner_p...@att.net
>> Subject: Re: $$Excel-Macros$$ Macro hangs
>> To: excel-macros@googlegroups.com
>>
>> Dave, keep in mind that Excel can do several HUNDRED comparisons each SECOND.
>>
>> If you're adding an Application.wait for only ONE second EACH LINE for
>> 13,000
>> lines,
>> you're adding 13,000 SECONDS, or 216 minutes, or 3.6 hours of WAIT TIME!
>>
>> so, I suspect that you're not "hanging", but simply waiting a LONG time.
>> and, during the seconds of waiting, the escape characters used to interrupt
>> aren't being received.
>>
>> Now.. personally, I like using excel "dictionaries" to store unique data.
>> I've done some pretty elaborate things.
>> I wrote a script to compare the fields and sum the columns.
>>
>> It runs (on my machine) in 1 minute, 19 seconds...
>>
>> try this (watch for wrapping from email):
>> it also displays a status line in the status bar.
>>
>> Sub DeleteDuplicateDict()
>> Dim RowCnt, R, Datainx, stat, msg
>> Dim Dict_E, Dict_F
>> Dim tstart, tstop, TMin, TSec, TElapsed
>>
>> tstart = Timer
>> Application.ScreenUpdating = False
>> Set Dict_E = CreateObject("Scripting.Dictionary")
>> Set Dict_F = CreateObject("Scripting.Dictionary")
>>
>> stat = Dict_E.RemoveAll
>> stat = Dict_F.RemoveAll
>>
>> ' Count the number of rows in sheet
>> RowCnt = ActiveCell.SpecialCells(xlLastCell).Row
>> 'Starting in the last row, process upwards
>> For R = RowCnt To 2 Step -1
>> If (R Mod 500 = 0) Then Application.StatusBar = "Processing: " & R
>> Datainx = ActiveSheet.Cells(R, "B").Value & ActiveSheet.Cells(R,
>> "C").Value & ActiveSheet.Cells(R, "D").Value
>> If (Datainx & "X" <> "X") Then 'If the data row is not blank
>> If (Not Dict_E.exists(Datainx)) Then
>> 'new data, add new record to dictionaries
>> Dict_E.Add Datainx, ActiveSheet.Cells(R, "E").Value
>> Dict_F.Add Datainx, ActiveSheet.Cells(R, "F").Value
>> Else
>> 'Existing records, update dictionaries
>> Dict_E.Item(Datainx) = Dict_E.Item(Datainx) +
>> ActiveSheet.Cells(R, "E").Value
>> Dict_F.Item(Datainx) = Dict_F.Item(Datainx) +
>> ActiveSheet.Cells(R, "F").Value
>> Rows(R).Delete Shift:=xlUp
>> End If
>> End If
>> Next R
>> ' Count rows remaining
>> RowCnt = Application.WorksheetFunction.CountA(Range("A:A"))
>> For R = 2 To RowCnt
>> If (R Mod 500 = 0) Then Application.StatusBar = "Updating: " & R & "
>> of
>>
>> " & RowCnt
>> Datainx = ActiveSheet.Cells(R, "B").Value & ActiveSheet.Cells(R,
>> "C").Value & ActiveSheet.Cells(R, "D").Value
>> 'update rows with Dictionary values
>> If (Dict_E.exists(Datainx)) Then
>> ActiveSheet.Cells(R, "E").Value = Dict_E.Item(Datainx)
>> ActiveSheet.Cells(R, "F").Value = Dict_F.Item(Datainx)
>> Else
>> Cells(R, "A").Select
>> MsgBox "Missing data for row: " & R
>> End If
>> Next R
>>
>> 'display processing time
>> tstop = Timer
>> TMin = 0
>> TElapsed = tstop - tstart
>> TMin = TElapsed \ 60
>> TSec = TElapsed Mod 60
>> msg = msg & Chr(13) & Chr(13)
>> If (TMin > 0) Then msg = msg & TMin & " mins "
>> msg = msg & TSec & " sec"
>> MsgBox msg
>> Application.StatusBar = False
>> Application.ScreenUpdating = True
>> End Sub
>>
>>
>> Paul
>> >
>> >From: Dave Bonallack <davebonall...@hotmail.com>
>> >To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com>
>> >Sent: Wed, September 29, 2010 5:14:06 AM
>> >Subject: $$Excel-Macros$$ Macro hangs
>> >
>> >Hi group,
>> >I'm hoping someone can help me with the attached workbook.
>> >I've written a macro that makes XL freeze.
>> >The need is to check the data for duplicates based on Columns B, C & D. If
>> >duplicates are found, their totals in Columns E & F are to be sumed, then
>> >the
>
>> >duplicate row deleted.
>> >I concatonate Cells B2, C2 & D2, then compare that with a concatonation of
>>cells
>>
>> >
>> >B3, C3 & D3, then B4, C4 & D4, and so on to the end of the data, dealing
>> >with
>
>> >duplicates as they come up. Then I start again with row 3, and so on until
>> >all
>>
>> >the data is checked. The macro takes a long time to run, so I report
>> >progress
>>in
>>
>> >
>> >Cells G1 and H1.
>> >Whenever I run this macro, it never gets past about line 10 before XL
>> >freezes,
>>
>> >and I have to use the Windows Task Manager to close it.
>> >There may be a better way of doing this, but my question is, why does it
>> >cause
>>
>> >XL to freeze? It seems a simple enough piece of code.
>> >You will notice 5 lines of code remmed out. When active, this inserts a 1
>>second
>>
>> >
>> >(approx) delay after each row has been checked, and the code runs without
>> >freezing, but of course, with 13000 rows, adds about 3.6 hours to the run
>> >time
>>
>> >of the macro.
>> >This happens with XL2003 and XL2007, and on another computer as well.
>> >Anyone have any ideas?
>> >Regards - Dave.
>> >--
>>>----------------------------------------------------------------------------------
>>-
>> >
>> >
>> >Some important links for excel users:
>> >1. Follow us on TWITTER for tips tricks and links :
>> >http://twitter.com/exceldailytip
>> >2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> >3. Excel tutorials at http://www.excel-macros.blogspot.com
>> >4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> >5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>> >
>> >To post to this group, send email to excel-macros@googlegroups.com
>> >
>> ><><><><><><><><><><><><><><><><><><><><><><>
>> >Like our page on facebook , Just follow below link
>> >http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>> >
>>
>> --
>>----------------------------------------------------------------------------------
>>-
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>>http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> Like our page on facebook , Just follow below link
>> http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>--
>----------------------------------------------------------------------------------
>
>Some important links for excel users:
>1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
>2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>3. Excel tutorials at http://www.excel-macros.blogspot.com
>4. Learn VBA Macros at http://www.quickvba.blogspot.com
>5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
>To post to this group, send email to excel-macros@googlegroups.com
>
><><><><><><><><><><><><><><><><><><><><><><>
>Like our page on facebook , Just follow below link
>http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
