Solved Excel macro to delete row

Dell / Latitude e6420
January 15, 2014 at 12:14:08
Specs: Win 7 Pro x64, I7 Sandy Bridge 2.201 GHz / 8072 MB
Hello all,
I know little to nothing about Excel macros. Here's what I want to do:

If column A contains a date then delete the entire row where column A has a date in it.

Can I get some help? I can use this to save a lot of time.

~winipcfg

ASCII Question, Get an ANSI


See More: Excel macro to delete row

Report •

#1
January 15, 2014 at 12:39:34
✔ Best Answer
The main trick to deleting Rows via VBA is to delete the rows from the bottom up. If you try to delete them from the top down, the counter gets all messed up.

e.g. Let's say you check each row by counting 1, 2, 3, 4, etc. If A3 contains a Date and you delete Row 3, then Row 4 becomes Row 3 and what used to be in A4 is now in A3. However, your counter has already incremented to 4, so the value that is now in A3 (formally A4) never gets checked.

If you check each row by counting 4, 3, 2, 1 then you can delete Row 3 with no other impact because A4 has already been checked.

Try this:

Sub Delete_Dates()
'Determine last row with data in Sheet1 Column A
  lastRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Loop through Rows in reverse order
    For nxtRw = lastRw To 1 Step -1
'Check for date
     If IsDate(Range("A" & nxtRw)) Then
'Delete the row
        Range("A" & nxtRw).EntireRow.Delete
     End If
    Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 15, 2014 at 12:44:51
Thank you. That worked like a charm!

~winipcfg

ASCII Question, Get an ANSI


Report •

#3
January 15, 2014 at 13:41:46
Also thanks for adding comments to the code explaining the steps.

~winipcfg

ASCII Question, Get an ANSI


Report •

Related Solutions

#4
January 15, 2014 at 13:49:02
The comments weren't just for you. I usually comment my code because I can't always remember what I'm trying to do. ;-)

I've got some macros from so long ago that I can't possibly remember how they work.

It gets even worse as your style of coding evolves and you look back at code you wrote a few years ago and don't even recognize it.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question