Hey,
It's not that much of a problem as the rarity of it happening is very slim BUT one that I would like to eradicate JUST in case as it stops my worksheet_change.
I'm using the following code to extract unique values from a list in A2:A & Last Row.. if I was to only add in 1 or 2 values to the list then I get the following error and it crashes the code. How can I prevent this?
ALSO, on the slim chance of having some duplicates, sometimes it would pull 1 duplicate for example..
Notice how there is 2 3's?
<tbody>
</tbody>
It's not that much of a problem as the rarity of it happening is very slim BUT one that I would like to eradicate JUST in case as it stops my worksheet_change.
I'm using the following code to extract unique values from a list in A2:A & Last Row.. if I was to only add in 1 or 2 values to the list then I get the following error and it crashes the code. How can I prevent this?
Code:
Sub UniqueCopy()
Dim sht As Worksheet
Dim lr As Long, lrc As Long
Set sht = ActiveWorkbook.Sheets(1)
Application.EnableEvents = False
Application.ScreenUpdating = False
sht.Range("I2:I1000").ClearContents
lr = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
If sht.Range("A2") = "" Then
'do nothing
Else
[B] sht.Range("A2:A" & lr).AdvancedFilter Action:=xlFilterCopy, copytorange:=sht.Range("I2"), unique:=True[/B]
End If
lrc = sht.Cells(sht.Rows.Count, "I").End(xlUp).Row
If sht.Range("A2") = "" Then
'do nothing
Else
sht.Range("I2:I" & lrc).Copy
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
ALSO, on the slim chance of having some duplicates, sometimes it would pull 1 duplicate for example..
Notice how there is 2 3's?
A | B | |
1 | Header1 | Header2 |
2 | 3 | 3 |
3 | 4 | 4 |
4 | 5 | 5 |
5 | 3 | 3 |
6 | 3 | 2 |
7 | 2 | 1 |
8 | 5 | |
9 | 5 | |
10 | 3 | |
11 | 5 | |
12 | 4 | |
13 | 1 | |
14 | 2 | |
15 | 4 | |
16 | 5 | |
17 | 3 | |
18 | 5 | |
19 | 2 | |
20 | 5 | |
21 | 3 | |
22 | 4 | |
23 | 4 | |
24 | 5 |
<tbody>
</tbody>