Delete Row if number is below 1 in column A

[Superstar31]

Using VB Code

Sub MyDelete()

Dim i As Long

For i = Range("A65536").End(xlUp).Row To 1 Step -1
If Not IsNumeric(Left(Cells(i, "A"), 1)) Then Rows(i).EntireRow.Delete
Next i

End Sub

written by Jmiskey

I am trying to edit it so that it scans column A and if any number is below 1, then it deletes the whole row.

The reason I am trying to delete any number below 1 is because some columns are numeric but no data is in them, however the rest of that row has information I need to delete.

??? hopefully this makes sense

 

[Superstar31]

I'll check it out in a minute, i get off work at 5pm, so I didn't look at it yet.

 

[Superstar31]

Oh, I think I understand.
Am I correct in thinking that these rows (with blank cells in column A) are at the bottom of the data table?
If so then the problem is that we're telling it to work on the rows from the last entry in column A and up.
This will start from the last row that has data in it, no matter which column it's in whether or not column A is blank.

Sub MyDelete()
Dim i As Long, LstRw As Long
LstRw = Cells.SpecialCells(xlCellTypeLastCell).Row
Application.ScreenUpdating = False
For i = LstRw To 2 Step -1
  If Not IsNumeric(Cells(i, 1)) Or Cells(i, 1) < 1 Then Rows(i).EntireRow.Delete
Next i
Application.ScreenUpdating = True
End Sub

Does that help or am I still not getting it?

Blank cells can be anywhere throught the worksheet, but they should mainly be at the bottom, because I have it sort by Account number.

I'm not sure if I understand what you mean by start with the last row that has data in it. Here is what I do..

I get a document that is all messed up, I then use text to column to structure it, after I do that, I remove the garabge lines at the top so that "account no" is on the first line. I then sort by account number and want to remove anything beyond my account numbers. So I guess in essence you are correct, all the garbage is at the bottom on the worksheet.

Your code does seem to do the job, let me try it on another worksheet that follows the same format.

It works like a charm, saves me at least an 1/2 with all my macros