Macro

[Neil]

How do I find a value (or values) in a range (column) and delte it /them when
found

All suggestions appreciated

 

[Ranjit kurian]

Do you want to identify the duplicate values, give us some example

 

[Joel]

Data = 345
Set found = Columns("A").Find(what:=Data, LookIn:=xlValues, lookat:=xlWhole)
If Not found Is Nothing Then
found.Delete shift:=xlShiftUp
End If

 

[Don Guillett]

Sub findanddelete()
mc = "a"
For i = Cells(rows.Count, mc).End(xlUp).Row To 1 Step -1
If Cells(i, mc) = 3 Then rows(i).Delete
Next i
End Sub

 

[ryguy7272]

Not as eloquent as some others, but this should get the job done:

Sub DeleteDuplicateRows()
'
' This macro deletes duplicate rows in the selection. Duplicates are
' counted in the COLUMN of the active cell.

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim n As Long
Dim v As Variant
Dim rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col = ActiveCell.Column

If Selection.Rows.count > 1 Then
Set rng = Selection
Else
Set rng = ActiveSheet.UsedRange.Rows
End If

n = 0
For r = rng.Rows.count To 1 Step -1
v = rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(rng.Columns(1), v) > 1 Then
rng.Rows(r).EntireRow.Delete
n = n + 1
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Regards,
Ryan---

 

[Neil]

great thanks for the quick response
--
Neil

Not as eloquent as some others, but this should get the job done:

Sub DeleteDuplicateRows()
'
' This macro deletes duplicate rows in the selection. Duplicates are
' counted in the COLUMN of the active cell.

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim n As Long
Dim v As Variant
Dim rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col = ActiveCell.Column

If Selection.Rows.count > 1 Then
Set rng = Selection
Else
Set rng = ActiveSheet.UsedRange.Rows
End If

n = 0
For r = rng.Rows.count To 1 Step -1
v = rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(rng.Columns(1), v) > 1 Then
rng.Rows(r).EntireRow.Delete
n = n + 1
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Regards,
Ryan---

 

[Neil]

thanks for the quick reply Don
--
Neil

Sub findanddelete()
mc = "a"
For i = Cells(rows.Count, mc).End(xlUp).Row To 1 Step -1
If Cells(i, mc) = 3 Then rows(i).Delete
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Neil]

Great thanks for the quick reply Joel
--
Neil

Data = 345
Set found = Columns("A").Find(what:=Data, LookIn:=xlValues, lookat:=xlWhole)
If Not found Is Nothing Then
found.Delete shift:=xlShiftUp
End If

 

[Neil]

thanks for the quick reply Ranjit - I have used the ideas from the other guys