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

 

[Crow_23]

Sub DeleteNumber()

Range("A65536").End(xlUp).Select
While ActiveCell.Row > 1
If ActiveCell < 1 Then ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
Wend

End Sub

Something like that what you looking for?

 

[HalfAce]

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.

Hello,
I'm not sure I understand.
Right now the code is looking at each cell in column A to determine if the first character is numeric and deleting the row if it's not.
I don't quite get your statement "some columns are numeric but no data is in them" - there has to be some kind of data in order for it to be numeric.

Are you wanting to go through column A and delete any row(s) in which the entire value of the cell is less than 1, or if just the first character is less than 1, or ... maybe the number of characters in the cell is less than 1?

 

[Superstar31]

Hello,
I'm not sure I understand.
Right now the code is looking at each cell in column A to determine if the first character is numeric and deleting the row if it's not.
I don't quite get your statement "some columns are numeric but no data is in them" - there has to be some kind of data in order for it to be numeric.

Are you wanting to go through column A and delete any row(s) in which the entire value of the cell is less than 1, or if just the first character is less than 1, or ... maybe the number of characters in the cell is less than 1?

Actually, it doesn't matter if the 1st character is numeric, just is there a numercial value in the column a, if there is a numerical value in column a, then leave the row alone, if there is not a numeric value in column a, then delete the row. the only problem with that, is some blank cells (at least to the naked eye) are not being detected, and thus the row the corresponds with them are not being deleted, so i figured the code i need, should look to see if there is a numeric value in column a above the #1, if so leave it alone, if column a has a numeric value less than 1, then delete the whole row:

 

[HalfAce]

Does this work for you?

Sub MyDelete()
Dim i As Long
Application.ScreenUpdating = False
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 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

Hope it helps.

 

[Superstar31]

Does this work for you?

Sub MyDelete()
Dim i As Long
Application.ScreenUpdating = False
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 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

Hope it helps.

Sorry no, Well kinda, it deletes the very 1st cells which holds the name accounts in it, but the lower cells that I want to erase its saving, do i need a range in there?

ok, i threw in randow cells at the top, some with numbers in column A, and some without, it delted the ones without number, so it works, but at the bottom on my data, are blank cells that are't being deleted?

Hmm, it delets if i put 0.9 which is what i wanted it to do, but its not touching any of the cells at the bottom on my sheet

ok, wait it worked this time? let me try again, ok not working again

 

[Superstar31]

Sub DeleteNumber()

Range("A65536").End(xlUp).Select
While ActiveCell.Row > 1
If ActiveCell < 1 Then ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
Wend

End Sub

Something like that what you looking for?

Sorry Crow, didn't see your post, it didn't work didn't delete much

 

[HalfAce]

Sorry no, Well kinda, it deletes the very 1st cells which holds the name accounts in it, but the lower cells that I want to erase its saving, do i need a range in there?

ok, i threw in randow cells at the top, some with numbers in column A, and some without, it delted the ones without number, so it works, but at the bottom on my data, are blank cells that are't being deleted?

Hmm, it delets if i put 0.9 which is what i wanted it to do, but its not touching any of the cells at the bottom on my sheet

ok, wait it worked this time? let me try again, ok not working again

Alright, we can prevent it from looking at (and deleting) row 1 where your header is by changing the line:
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
to this:
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1

The rest of your comments have me kinda confused.
Are you ending up with rows that have data in them when the cell in column A is either text, or a number less than 1, or blank? - (Or are you trying to get rid of all the (completely) empty rows below your data range?)

 

[Superstar31]

The rest of your comments have me kinda confused.
Are you ending up with rows that have data in them when the cell in column A is either text, or a number less than 1, or blank? - (Or are you trying to get rid of all the (completely) empty rows below your data range?)

I am ending up with date in cells that are blank in column 1 but rest of the row has information.

Some of the rows are blank, but if there are empty rows at the bottom thats fine.[/quote]

 

[HalfAce]

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?