Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
andreeadede
 | Femeie
24 ani
Ilfov
cauta Barbat
24 - 39 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 18 ani
Postari: 2247
|
|
If you want to delete duplicates in selection without deleting any rows, use:
Source:
Sub SelectDeleteDups2()
'PURPOSE: Remove duplicate cell values within a selected cell range
Dim rng As Range
Dim x As Integer
'Optimize code execution speed
Application.ScreenUpdating = False
'Determine range to look at from user's selection
On Error GoTo InvalidSelection
Set rng = Selection
On Error GoTo 0
'Determine if multiple columns have been selected
If rng.Columns.Count > 1 Then
On Error GoTo InputCancel
x = InputBox("Multiple columns were detected in your selection. " & _
"Which column should I look at? (Number only!)", "Multiple Columns Found!", 1)
On Error GoTo 0
Else
x = 1
End If
'Optimize code execution speed
Application.Calculation = xlCalculationManual
'Remove entire row
rng.RemoveDuplicates Columns:=x
'Change calculation setting to Automatic
Application.Calculation = xlCalculationAutomatic
Exit Sub
'ERROR HANDLING
InvalidSelection:
MsgBox "You selection is not valid", vbInformation
Exit Sub
InputCancel:
End Sub |
_______________________________________
|
|
| pus acum 11 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 795
|
|
Select a range, start macro then specify the column number where you have duplicates that need removal, then click ok and it will remove rows too in the other column:
Sub SelectDeleteDups()
Application.ScreenUpdating = False
Dim MyRange As Range
Dim varInput As String
Set MyRange = ActiveSheet.Range(Selection, ActiveCell.SpecialCells(xlLastCell))
varInput = InputBox("Select column number:")
On Error Resume Next
MyRange.RemoveDuplicates Columns:=(varInput), Header:=xlYes
Application.ScreenUpdating = True
End Sub |
Modificat de TRaP (acum 6 ani)
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 795
|
|
If you want to check if data from multiple columns is duplicated.
Say you have A and B columns and want to see if there is a duplicate for A and B, not for an entry in A or an entry in B, but for both.
You can concatenate the values to column C, then check if column C has duplicates.
Modificat de TRaP (acum 6 ani)
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 795
|
|
Find duplicates: With conditional formatting.
Remove duplicates: Go to Data tab and in the center of the ribbon you have a button called "Remove duplicates"
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 795
|
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 795
|
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 795
|
|
For the code in 1st post, if you receive an error on this line: rng.RemoveDuplicates Columns:=x
stating that:
"You cannot remove duplicate information when the range has an outline. To remove duplicates, clear the outline and perform the remove duplicates operation again."
Add the following line before the rng.RemoveDuplicates Columns:=x line:
ActiveSheet.Cells.ClearOutline '= IF YOU RECEIVE ERROR TO CLEAR OUTLINE
|
|
| pus acum 5 ani |
|