Excel VBA delete rows w/integers

August 10, 2009 at 13:04:32
Specs: Windows Vista, 1GB
How to delete rows in Excel 2003 worksheet that only have integers as data

See More: Excel VBA delete rows w/integers

Report •

August 10, 2009 at 18:51:02
This should delete rows where the value in Column A is an integer while leaving text and non-integer values alone.

Sub DelInts()
 lastRow = Range("A" & Rows.Count).End(xlUp).Row
  For nxtRow = lastRow To 1 Step -1
   If IsNumeric(Cells(nxtRow, 1)) Then
    If Int(Cells(nxtRow, 1)) = Cells(nxtRow, 1) Then
     Cells(nxtRow, 1).EntireRow.Delete
    End If
   End If
End Sub

Report •

August 11, 2009 at 12:01:40
Ah, man you are a lifesaver...I know zilch about this stuff. One small issue...I am getting the following compile error msg "End If without block If". What did I do wrong? thanks AGAIN!

Report •

August 11, 2009 at 12:23:36
Update to previous reply:
I was able to stop the "End If without block If" msg. The macro works, after launching into the debugger mode. What is up with that?

Report •

Related Solutions

August 11, 2009 at 12:48:24
It might depend on how the code ended up in the VBA editor after pasting.

I just did a copy/paste from my post directly into the VBA editor and it worked fine.

You'll get that error if the complier can't match up an End If to a Block If as follows:

If (this is true) Then (do this)

is not a Block If

If (this is true) Then
   (do this)
   (do this too)
End If

is a Block If.

Typically you'd use a Block If to do more than one thing if the If is True, but it can also be used to make the code more readable if the If line is really long. For example, the Block If in the middle of the code I suggested doesn't need to be a Block If, but it would be a really long line and hard to read otherwise.

Report •

August 12, 2009 at 14:11:38
Your examples and explanation are excellent.

What books or reference material can you suggest for someone who is not logically minded, but who would like to learn how to use VBA in Excel?

Thanks a milliion+ !!


Report •

Ask Question