How can I delete the whole row of an Excel sheet, if in the column G has a number that starts with 210.
I don't want to delete the row if the cell has 210 somewhere inside, but only when start with it.
-
It can't be done by using formulas - I mean delete, only find out such rows. Is VBA an option for you?Peter L.– Peter L.2013-02-06 19:19:27 +00:00Commented Feb 6, 2013 at 19:19
-
I don't know it but tell me what to search for if you cantequilaras– tequilaras2013-02-06 19:25:45 +00:00Commented Feb 6, 2013 at 19:25
-
are the numbers stored as numbers or text? (i.e. by default, are they displayed left- or right-aligned?)Peter Albert– Peter Albert2013-02-06 20:27:52 +00:00Commented Feb 6, 2013 at 20:27
Add a comment
|
2 Answers
Use this code:
Sub RemoveRows()
Dim i As Long
i = 1
Do While i <= ThisWorkbook.ActiveSheet.Range("G1").CurrentRegion.Rows.Count
If Left(ThisWorkbook.ActiveSheet.Range("G" & i).Formula, 3) = "210" Then
ThisWorkbook.ActiveSheet.Cells(i, 1).EntireRow.Delete
Else
i = i + 1
End If
Loop
End Sub
Sample file: https://www.dropbox.com/s/yp2cwphhhdn3l98/RemoweRows210.xlsm
To see it and run, press ALT-F11, open Module1 and press F5. Good luck!
Comments
In case you want to do it without code but purely in the UI, this is how you could do this pretty efficiently:
- Insert a temporary column (e.g. right of column G) (Ctrl-Space in any cell in column H, Ctrl-+)
- Fill the column with the formula
=LEFT(TEXT(G1),3)="210"- this will returnTRUEfor all rows you look for - Apply an AutoFilter to either that new column or the full range (Ctrl-Shift-L)
- Filter that column for
TRUE- this way, only the rows you wish to delete remain - Select all rows and delete (Ctrl-A in any cell in the table, Shift-Space, Ctrl--)
- Delete the temporary column (Ctrl-Space in any cell in column H, Ctrl--)
Done!
Comments
