Function takes damn long! sorry for @#$#@

[nrage21]

I have the beginning of a list of names which could grow to 24000 rows
I defined a range = range1 = C424004
I place a function to flag duplicate records...
i.e. =IF(COUNTIF(Range1,C4)>1,"Duplicate","")

When I add or delete a record, excell begins calculating cells, thi
takes quite a while (over a minute). How can I improve the speed? I
there anything better that I can use as a function?

TIA
- Larry -
VBA Amateu

 

[Frank Kabel]

Hi
i doubt there is something faster (if you compare over 24000 rows)
In you case you may disable Autocalculation while entering new data

 

[nrage21]

I see,

Can you at least give me a pointer, how to not calculate blank rows???

- Larry -
VBA Amateur

 

[Frank Kabel]

Hi
normally COUNTIF should internally skip blank rows

 

[cogent]

Try

Range1 =
INDIRECT(+ADDRESS(4,3,,,"Sheet1")&":"&ADDRESS(+COUNTA(Sheet1!$c:$c),4))

You will only search rows which are inhabited.

Remember when you copy down an equation like your "if(countif(..." each
cell ostensibly represents a "macro." You would be better off writing an
equation to accomplish the duplicate flagging rather than copying the
formula down rows of cells.

W

 

[nrage21]

Cogent,
Where do you enter...?? what cell?? How??
Range1 =
INDIRECT(+ADDRESS(4,3,,,"Sheet1")&":"&ADDRESS(+COUNTA(Sheet1!$c:$c),4))

Could you please give me some instructions??

Is it..??
=IF(COUNTIF(INDIRECT(+ADDRESS(4,3,,,"Sheet1")&":"&ADDRESS(+COUNTA(Sheet1!$C:$D),4))>1,"Duplicate","")


- Larry - (puzzled face)
VBA Amateu

 

[nrage21]

Cogent,
Where do you enter...?? what cell?? How?? Do you do a fill down??
Range1 =
INDIRECT(+ADDRESS(4,3,,,"Sheet1")&":"&ADDRESS(+COUNTA(Sheet1!$c:$c),4))

Could you please give me some instructions??

Is it..??
=IF(COUNTIF(INDIRECT(+ADDRESS(4,3,,,"Sheet1")&":"&ADDRESS(+COUNTA(Sheet1!$C:$D),4))>1,"Duplicate","")


- Larry - (puzzled face)
VBA Amateu

 

[cogent]

There is no fill down for setting Range1.

Use the commands Insert | Name | Define

Type in Range1 in the Range Name area, then type the formula in at the
bottom of the dialog box.

Be careful to enter exactly. The address function has a lot of inputs.

Now, Range1 will automatically adapt, based upon the number of rows picked
up by the counta function. This way you are not calculating excess rows.

There was another answer on the board for finding and eliminating dupes. I
will post it here when I find it.

W

 

[cogent]

I adapted this routine from the routine written by Frank Kabel, Frankfurt
Germany. I changed the calculation of LastRow only.

Sub delete_dupe_rows()
Dim RowNdx As Long
Dim LastRow As Integer
LastRow = ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, 1).Value = "DupeValue" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub

W

 

[nrage21]

Cogent,
Thanks for your help and efforts... however I never said anything abou
eliminating duplicates. Duplicates in my case are OK, I just wanted t
know if there was a faster way to avoid waiting for calculation to end
since I have 24000 rows that keep changing everyday. I just need to b
able to flag them, that's all.

I'm going to look at Name=>Define see where that leads me.

- Larry -
VBA Amateu