I have found an interesting macro to format excel. Suppose, I have an excel file just like this:
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.
| 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 |
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:
Post a Comment