Macro to delete row

P

puiuluipui

Hi, i need a macro to delete rows that don't have in "H" column the name "John"
If in "H" column the code finds anything else, then to delete row.
Can this be done?
Thanks!
 
×

מיכ×ל (מיקי) ×בידן

Hi,
One approach might be:
=================
Sub John()
LR = [H65536].End(xlUp).Row
For R = LR To 1 Step -1
If Cells(R, 8) <> "John" Then Cells(R, 8).EntireRow.Delete
Next
End Sub
========
Micky
 
J

Jacob Skaria

Hi

'Delete if not cell match
Sub DeleteRows()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, "H").End(xlUp).Row To 1 Step -1
If StrComp(Range("H" & lngRow), "John", vbTextCompare) <> 0 Then
Rows(lngRow).Delete
End If
Next
End Sub


'delete rows if the word 'john' is not found within the cell
Sub DeleteRows()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, "H").End(xlUp).Row To 1 Step -1
If InStr(1, Range("H" & lngRow), "john", vbTextCompare) = 0 Then
Rows(lngRow).Delete
End If
Next
End Sub
 
D

Don Guillett

sub delifnotjohn()'if John is not within other text
for i=cells(rows.count,"H").end(xlup).row to 1 step -1
if ucase(cells(i,"h"))<>"JOHN" then rows(i).delete
next i
end sub
 
Top