CODEWITHSUNDEEP
vbaexceldelete-row
tequilaras
tequilaras

Reputation: 279

excel delete rows in some cases

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.

Upvotes: 0

Views: 632

Answers (2)

Peter Albert
Peter Albert

Reputation: 17505

In case you want to do it without code but purely in the UI, this is how you could do this pretty efficiently:

  1. Insert a temporary column (e.g. right of column G) (Ctrl-Space in any cell in column H, Ctrl-+)
  2. Fill the column with the formula =LEFT(TEXT(G1),3)="210" - this will return TRUE for all rows you look for
  3. Apply an AutoFilter to either that new column or the full range (Ctrl-Shift-L)
  4. Filter that column for TRUE - this way, only the rows you wish to delete remain
  5. Select all rows and delete (Ctrl-A in any cell in the table, Shift-Space, Ctrl--)
  6. Delete the temporary column (Ctrl-Space in any cell in column H, Ctrl--)

Done!

Upvotes: 1

Peter L.
Peter L.

Reputation: 7304

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!

Upvotes: 3

Related Questions