Help in macro coding

  • Thread starter Thread starter Sasikiran
  • Start date Start date
S

Sasikiran

Dear,

I am working on a macro which trims the raw data into a desired one.

i would require your help in adding the macro code to delete the entire row
if it does not matches to my requirement.

Say Column D has data which gives the name of the individuals. I would only
require the rows which has the below mentioned names in the column D. Like
John, Peter, Sandra and Kate.

The macro code should identify these names, keep the rows having these and
delete all other rows which do not match to these names.

Please help.
 
Hi,

try this

Sub Versive()
Dim R As Range
Dim V As Variant
Dim S As String
Dim CopyRange As Range
Dim LastRow As Long
Set Sht = Sheets("Sheet1") ' Change to suit
LastRow = Sht.Cells(Cells.Rows.Count, "D").End(xlUp).Row
S = "Peter,Sandra,Kate"
V = Split(S, ",")
For Each R In Sht.Range("D1:D" & LastRow)
If IsError(Application.Match(CStr(R.Value), V, 0)) Then
If CopyRange Is Nothing Then
Set CopyRange = R.EntireRow
Else
Set CopyRange = Union(CopyRange, R.EntireRow)
End If
End If
Next R

If Not CopyRange Is Nothing Then
CopyRange.Delete
End If

End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Thank you so much it is working :)

Mike H said:
Hi,

try this

Sub Versive()
Dim R As Range
Dim V As Variant
Dim S As String
Dim CopyRange As Range
Dim LastRow As Long
Set Sht = Sheets("Sheet1") ' Change to suit
LastRow = Sht.Cells(Cells.Rows.Count, "D").End(xlUp).Row
S = "Peter,Sandra,Kate"
V = Split(S, ",")
For Each R In Sht.Range("D1:D" & LastRow)
If IsError(Application.Match(CStr(R.Value), V, 0)) Then
If CopyRange Is Nothing Then
Set CopyRange = R.EntireRow
Else
Set CopyRange = Union(CopyRange, R.EntireRow)
End If
End If
Next R

If Not CopyRange Is Nothing Then
CopyRange.Delete
End If

End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
Click to expand...
 
Back
Top