Delete Row if number is below 1 in column A

Superstar31

Active Member
Joined
Nov 10, 2005
Messages
496
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Crow_23

Board Regular
Joined
Feb 17, 2005
Messages
183
Code:
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Superstar31 said:
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

Active Member
Joined
Nov 10, 2005
Messages
496
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453

ADVERTISEMENT

Does this work for you?
Code:
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

Active Member
Joined
Nov 10, 2005
Messages
496
HalfAce said:
Does this work for you?
Code:
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

Active Member
Joined
Nov 10, 2005
Messages
496

ADVERTISEMENT

Crow_23 said:
Code:
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Superstar31 said:
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

Active Member
Joined
Nov 10, 2005
Messages
496
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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.
Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,499
Members
412,670
Latest member
Khin Zaw Htwe
Top