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. 		Nou pe simpatie:
Ioanacatalina la Simpatie.ro
Femeie
25 ani
Salaj
cauta Barbat
25 - 61 ani
Mrrrr's Forum (VIEW ONLY)ReguliInregistrareLoginPozeNu sunteti logat. Lista Forumurilor Pe Tematici
Mrrrr's Forum (VIEW ONLY) / Tutoriale si Ghiduri Utile // Tutorials and useful guides / [EXCEL] Find and remove duplicates [VBA, Conditional Formatting] Pagini: 1 Moderat de TRaP, TonyTzu
#1
Mrrrr
AdMiN
Postari: 2388
If you want to delete duplicates in selection without deleting any rows, use:

Source:
https://www.thespreadsheetguru.com/the- ... ntire-rows


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


_______________________________________
 
   
#2
TRaP
Moderator
Postari: 959
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
 
   
#3
TRaP
Moderator
Postari: 959
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.
 
   
#4
TRaP
Moderator
Postari: 959
Find duplicates: With conditional formatting.
http://www.wikihow.com/Find-Duplicates-in-Excel

Remove duplicates: Go to Data tab and in the center of the ribbon you have a button called "Remove duplicates"
http://www.excel-easy.com/examples/remo ... cates.html
 
   
#5
TRaP
Moderator
Postari: 959
Updated
 
   
#6
TRaP
Moderator
Postari: 959
Updated
 
   
#7
TRaP
Moderator
Postari: 959
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
 
   
Pagini: 1  
Mergi la