zouhair_psi
02-16-2017, 01:40 PM
Hello, I would like to apologize first for my bad english
I need help editing a VBA macro.
The macro deletes all duplicates per cell (except the first one) on all columns,raws and starts with the first row.
I would like to change the macro to take the last row only as reference
this is the macro
Sub DeleteDuplicateEntries()
Dim Cell As Range, Cel As Range, N&
Application.ScreenUpdating = False
N = 0
For Each Cell In Selection
'1st loop - (to speed things up ignore any empty cells)
If Cell <> Empty Then
For Each Cel In Selection
'2nd loop - compare non-empty cel values
'and clear contents if it's a duplicated value
If Cel <> Empty And _
Cel.Value = Cell.Value And _
Cel.Address <> Cell.Address Then
Cel.ClearContents
N = N + 1
End If
Next Cel
End If
Next
Application.ScreenUpdating = True
MsgBox "There were " & N & " duplicated entries deleted"
End Sub
for example
A B C D E F G
10
17
25
29
38
44
45
6
9
28
34
35
41
47
9
12
19
26
32
33
38
9
16
26
29
34
43
44
become
A B C D E F G
10
17
25
38
45
6
28
35
41
47
12
19
32
33
38
9
16
26
29
34
43
44
thnx for your help and time
I need help editing a VBA macro.
The macro deletes all duplicates per cell (except the first one) on all columns,raws and starts with the first row.
I would like to change the macro to take the last row only as reference
this is the macro
Sub DeleteDuplicateEntries()
Dim Cell As Range, Cel As Range, N&
Application.ScreenUpdating = False
N = 0
For Each Cell In Selection
'1st loop - (to speed things up ignore any empty cells)
If Cell <> Empty Then
For Each Cel In Selection
'2nd loop - compare non-empty cel values
'and clear contents if it's a duplicated value
If Cel <> Empty And _
Cel.Value = Cell.Value And _
Cel.Address <> Cell.Address Then
Cel.ClearContents
N = N + 1
End If
Next Cel
End If
Next
Application.ScreenUpdating = True
MsgBox "There were " & N & " duplicated entries deleted"
End Sub
for example
A B C D E F G
10
17
25
29
38
44
45
6
9
28
34
35
41
47
9
12
19
26
32
33
38
9
16
26
29
34
43
44
become
A B C D E F G
10
17
25
38
45
6
28
35
41
47
12
19
32
33
38
9
16
26
29
34
43
44
thnx for your help and time