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

All suggestions appreciated
--
Neil

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


"Neil" wrote:

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

All suggestions appreciated
--
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" wrote:

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

All suggestions appreciated
--
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

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

All suggestions appreciated
--
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---

--
RyGuy


"Don Guillett" wrote:

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

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

All suggestions appreciated
--
Neil

great thanks for the quick response
--
Neil


"ryguy7272" wrote:

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---

--
RyGuy


"Don Guillett" wrote:

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

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

All suggestions appreciated
--
Neil

thanks for the quick reply Don
--
Neil


"Don Guillett" wrote:

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

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

All suggestions appreciated
--
Neil

Great thanks for the quick reply Joel
--
Neil


"Joel" wrote:


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" wrote:

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

All suggestions appreciated
--
Neil

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


"Ranjit kurian" wrote:

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


"Neil" wrote:

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

All suggestions appreciated
--
Neil