Tuesday, July 1, 2014

Delete Particular Rows in Excel

I have found an interesting macro to format excel.  Suppose, I have an excel file just like this:





SL Roll Name GPA Year
1 33001 Tukhrejul Inam 3.27 2008
2 33002 Ali Hossain 3.87 2007
4 33003 Jahangir Hossain 3.54 2006
5 33004 Moyen Hossain 3.29 2009
I want to delete all rows which contain name like ‘Hossain’. Then I hae to run a macro just like this:
Sub Test()
Dim Rng As Range
Dim x As Long
Set Rng = Range(“C1:C” & Range(“A65536″).End(xlUp).Row)
For x = Rng.Rows.Count To 1 Step -1
If InStr(1, Rng.Cells(x, 1).Value, “Hossain“) = 1 Then
Rng.Cells(x, 1).EntireRow.Delete
End If
Next x
End Sub
Here, C1:C will be column which contains the particular text. =1 means all rows which have the match, will be deleted. If I use ’0′, then all rows which don’t have the match, will be deleted.

No comments: