Delete entire row with a positive number
 

How do I delete an enitre row based on one columns number. For example, if
column A has a 5 then the entire row should be deleted and the formula move
to the next row.
This is what i have, but it is siftong through columns.

Sub click()
Dim i As Integer
Sheet1.Select
Sheet1.Range("a2:b5").Select
For i = 2 To 6
Cells(2, i).Select
If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete
Next i
End Sub

PLEASE HELP

Delete entire row with a positive number
 

something like this should work
with sheet1
for i=5 to 1 step -1
if cells(i,"a")0 then rows(i).delete
end with

--
Don Guillett
SalesAid Software

"marthasanchez" wrote in message
...
How do I delete an enitre row based on one columns number. For example, if
column A has a 5 then the entire row should be deleted and the formula
move
to the next row.
This is what i have, but it is siftong through columns.

Sub click()
Dim i As Integer
Sheet1.Select
Sheet1.Range("a2:b5").Select
For i = 2 To 6
Cells(2, i).Select
If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete
Next i
End Sub

PLEASE HELP

Delete entire row with a positive number
 

You might also want to try

.... Sheet1.Rows(i).Delete Shift:=xlUp

HTH
--
Allllen


"marthasanchez" wrote:

How do I delete an enitre row based on one columns number. For example, if
column A has a 5 then the entire row should be deleted and the formula move
to the next row.
This is what i have, but it is siftong through columns.

Sub click()
Dim i As Integer
Sheet1.Select
Sheet1.Range("a2:b5").Select
For i = 2 To 6
Cells(2, i).Select
If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete
Next i
End Sub

PLEASE HELP

Delete entire row with a positive number
 

Explanation:

Cells(row,column)
You have Cells(row, i), so i represents your column.
You just have to put it the other way around to sift through rows.

Solution:

Sub click()
Dim i As Integer
Sheet1.Select
Sheet1.Range("a2:b5").Select '<-You probably don't need this line of code
For i = 2 To 6 '<-Are you sure it is to 6 and not 5?
Cells(i, 2).Select '<-CHANGED, but also try it without this
line of code
If Sheet1.Cells(i, 2) 0 Then Sheet1.Rows(i).Delete '<-CHANGED
Next i
End Sub




--
Allllen


"marthasanchez" wrote:

How do I delete an enitre row based on one columns number. For example, if
column A has a 5 then the entire row should be deleted and the formula move
to the next row.
This is what i have, but it is siftong through columns.

Sub click()
Dim i As Integer
Sheet1.Select
Sheet1.Range("a2:b5").Select
For i = 2 To 6
Cells(2, i).Select
If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete
Next i
End Sub

PLEASE HELP

Delete entire row with a positive number
 

Martha, the main problem is that as you delete a row, you end up
skipping the next row, for example if B2 = 5, and B3 = 5, first you
delete row 2, now B2 holds the value that was in B3 and you said to go
to "Next i" which means the original value from B3 never gets checked.
Also the syntax for the Cells object is backwards, Cells(2,i) where i =
6 refers to Row 2, Column 6. The best approach to deleting rows is to
move backwards through them:
Sub click()
Dim i as Integer
With Sheet1
For i = 6 to 2 Step -1
If .Range("B" & i) 0 Then .Rows(i).Delete
Next
End Sub

HTH

Charles Chickering

marthasanchez wrote:
How do I delete an enitre row based on one columns number. For example, if
column A has a 5 then the entire row should be deleted and the formula move
to the next row.
This is what i have, but it is siftong through columns.

Sub click()
Dim i As Integer
Sheet1.Select
Sheet1.Range("a2:b5").Select
For i = 2 To 6
Cells(2, i).Select
If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete
Next i
End Sub

PLEASE HELP

Delete entire row with a positive number
 

Oops forgot "End With"

Sub click()
Dim i as Integer
With Sheet1
For i = 6 to 2 Step -1
If .Range("B" & i) 0 Then .Rows(i).Delete
Next
End With
End Sub

Charles

Die_Another_Day wrote:
Martha, the main problem is that as you delete a row, you end up
skipping the next row, for example if B2 = 5, and B3 = 5, first you
delete row 2, now B2 holds the value that was in B3 and you said to go
to "Next i" which means the original value from B3 never gets checked.
Also the syntax for the Cells object is backwards, Cells(2,i) where i =
6 refers to Row 2, Column 6. The best approach to deleting rows is to
move backwards through them:
Sub click()
Dim i as Integer
With Sheet1
For i = 6 to 2 Step -1
If .Range("B" & i) 0 Then .Rows(i).Delete
Next
End Sub

HTH

Charles Chickering

marthasanchez wrote:
How do I delete an enitre row based on one columns number. For example, if
column A has a 5 then the entire row should be deleted and the formula move
to the next row.
This is what i have, but it is siftong through columns.

Sub click()
Dim i As Integer
Sheet1.Select
Sheet1.Range("a2:b5").Select
For i = 2 To 6
Cells(2, i).Select
If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete
Next i
End Sub

PLEASE HELP

Delete entire row with a positive number
 

Thank-you!

Quick question

"Allllen" wrote:

Explanation:

Cells(row,column)
You have Cells(row, i), so i represents your column.
You just have to put it the other way around to sift through rows.

Solution:

Sub click()
Dim i As Integer
Sheet1.Select
Sheet1.Range("a2:b5").Select '<-You probably don't need this line of code
For i = 2 To 6 '<-Are you sure it is to 6 and not 5?
Cells(i, 2).Select '<-CHANGED, but also try it without this
line of code
If Sheet1.Cells(i, 2) 0 Then Sheet1.Rows(i).Delete '<-CHANGED
Next i
End Sub




--
Allllen


"marthasanchez" wrote:

How do I delete an enitre row based on one columns number. For example, if
column A has a 5 then the entire row should be deleted and the formula move
to the next row.
This is what i have, but it is siftong through columns.

Sub click()
Dim i As Integer
Sheet1.Select
Sheet1.Range("a2:b5").Select
For i = 2 To 6
Cells(2, i).Select
If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete
Next i
End Sub

PLEASE HELP

Delete entire row with a positive number
 

Isn't that the same as my post?

--
Don Guillett
SalesAid Software

"Die_Another_Day" wrote in message
oups.com...
Martha, the main problem is that as you delete a row, you end up
skipping the next row, for example if B2 = 5, and B3 = 5, first you
delete row 2, now B2 holds the value that was in B3 and you said to go
to "Next i" which means the original value from B3 never gets checked.
Also the syntax for the Cells object is backwards, Cells(2,i) where i =
6 refers to Row 2, Column 6. The best approach to deleting rows is to
move backwards through them:
Sub click()
Dim i as Integer
With Sheet1
For i = 6 to 2 Step -1
If .Range("B" & i) 0 Then .Rows(i).Delete
Next
End Sub

HTH

Charles Chickering

marthasanchez wrote:
How do I delete an enitre row based on one columns number. For example,
if
column A has a 5 then the entire row should be deleted and the formula
move
to the next row.
This is what i have, but it is siftong through columns.

Sub click()
Dim i As Integer
Sheet1.Select
Sheet1.Range("a2:b5").Select
For i = 2 To 6
Cells(2, i).Select
If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete
Next i
End Sub

PLEASE HELP

Delete entire row with a positive number
 

Close, yours would error out as you forgot the next statement, although
that wasn't my point for posting, I was trying to show th OP what was
wrong with her code and how to correct it.
Charles
Don Guillett wrote:
Isn't that the same as my post?

--
Don Guillett
SalesAid Software

"Die_Another_Day" wrote in message
oups.com...
Martha, the main problem is that as you delete a row, you end up
skipping the next row, for example if B2 = 5, and B3 = 5, first you
delete row 2, now B2 holds the value that was in B3 and you said to go
to "Next i" which means the original value from B3 never gets checked.
Also the syntax for the Cells object is backwards, Cells(2,i) where i =
6 refers to Row 2, Column 6. The best approach to deleting rows is to
move backwards through them:
Sub click()
Dim i as Integer
With Sheet1
For i = 6 to 2 Step -1
If .Range("B" & i) 0 Then .Rows(i).Delete
Next
End Sub

HTH

Charles Chickering

marthasanchez wrote:
How do I delete an enitre row based on one columns number. For example,
if
column A has a 5 then the entire row should be deleted and the formula
move
to the next row.
This is what i have, but it is siftong through columns.

Sub click()
Dim i As Integer
Sheet1.Select
Sheet1.Range("a2:b5").Select
For i = 2 To 6
Cells(2, i).Select
If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete
Next i
End Sub

PLEASE HELP

Delete entire row with a positive number
 

also Don's had no period in front of the cells or rows, so it would leave the
impression of working on Sheet1, but actually worked on the activesheet or if
in a sheet module, on the sheet containing the code. No problem if the
activesheet is sheet1 or sheet1 contains the code, but then you don't need
the with.

--
Regards,
Tom Ogilvy


"Die_Another_Day" wrote:

Close, yours would error out as you forgot the next statement, although
that wasn't my point for posting, I was trying to show th OP what was
wrong with her code and how to correct it.
Charles
Don Guillett wrote:
Isn't that the same as my post?

--
Don Guillett
SalesAid Software

"Die_Another_Day" wrote in message
oups.com...
Martha, the main problem is that as you delete a row, you end up
skipping the next row, for example if B2 = 5, and B3 = 5, first you
delete row 2, now B2 holds the value that was in B3 and you said to go
to "Next i" which means the original value from B3 never gets checked.
Also the syntax for the Cells object is backwards, Cells(2,i) where i =
6 refers to Row 2, Column 6. The best approach to deleting rows is to
move backwards through them:
Sub click()
Dim i as Integer
With Sheet1
For i = 6 to 2 Step -1
If .Range("B" & i) 0 Then .Rows(i).Delete
Next
End Sub

HTH

Charles Chickering

marthasanchez wrote:
How do I delete an enitre row based on one columns number. For example,
if
column A has a 5 then the entire row should be deleted and the formula
move
to the next row.
This is what i have, but it is siftong through columns.

Sub click()
Dim i As Integer
Sheet1.Select
Sheet1.Range("a2:b5").Select
For i = 2 To 6
Cells(2, i).Select
If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete
Next i
End Sub

PLEASE HELP

Delete entire row with a positive number
 

OK. Maybe I was a bit hasty.... So solly

--
Don Guillett
SalesAid Software

"Tom Ogilvy" wrote in message
...
also Don's had no period in front of the cells or rows, so it would leave
the
impression of working on Sheet1, but actually worked on the activesheet or
if
in a sheet module, on the sheet containing the code. No problem if the
activesheet is sheet1 or sheet1 contains the code, but then you don't need
the with.

--
Regards,
Tom Ogilvy


"Die_Another_Day" wrote:

Close, yours would error out as you forgot the next statement, although
that wasn't my point for posting, I was trying to show th OP what was
wrong with her code and how to correct it.
Charles
Don Guillett wrote:
Isn't that the same as my post?

--
Don Guillett
SalesAid Software

"Die_Another_Day" wrote in message
oups.com...
Martha, the main problem is that as you delete a row, you end up
skipping the next row, for example if B2 = 5, and B3 = 5, first you
delete row 2, now B2 holds the value that was in B3 and you said to
go
to "Next i" which means the original value from B3 never gets
checked.
Also the syntax for the Cells object is backwards, Cells(2,i) where i
=
6 refers to Row 2, Column 6. The best approach to deleting rows is to
move backwards through them:
Sub click()
Dim i as Integer
With Sheet1
For i = 6 to 2 Step -1
If .Range("B" & i) 0 Then .Rows(i).Delete
Next
End Sub

HTH

Charles Chickering

marthasanchez wrote:
How do I delete an enitre row based on one columns number. For
example,
if
column A has a 5 then the entire row should be deleted and the
formula
move
to the next row.
This is what i have, but it is siftong through columns.

Sub click()
Dim i As Integer
Sheet1.Select
Sheet1.Range("a2:b5").Select
For i = 2 To 6
Cells(2, i).Select
If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete
Next i
End Sub

PLEASE HELP

Delete entire row with a positive number
 

Thank-you for the awesome feedback. I have read that the best way to sifr
through the data is from the end of the recordset to the beggining. How do I
do this if the number in the recordset changes each time the program is run?

"Tom Ogilvy" wrote:

also Don's had no period in front of the cells or rows, so it would leave the
impression of working on Sheet1, but actually worked on the activesheet or if
in a sheet module, on the sheet containing the code. No problem if the
activesheet is sheet1 or sheet1 contains the code, but then you don't need
the with.

--
Regards,
Tom Ogilvy


"Die_Another_Day" wrote:

Close, yours would error out as you forgot the next statement, although
that wasn't my point for posting, I was trying to show th OP what was
wrong with her code and how to correct it.
Charles
Don Guillett wrote:
Isn't that the same as my post?

--
Don Guillett
SalesAid Software

"Die_Another_Day" wrote in message
oups.com...
Martha, the main problem is that as you delete a row, you end up
skipping the next row, for example if B2 = 5, and B3 = 5, first you
delete row 2, now B2 holds the value that was in B3 and you said to go
to "Next i" which means the original value from B3 never gets checked.
Also the syntax for the Cells object is backwards, Cells(2,i) where i =
6 refers to Row 2, Column 6. The best approach to deleting rows is to
move backwards through them:
Sub click()
Dim i as Integer
With Sheet1
For i = 6 to 2 Step -1
If .Range("B" & i) 0 Then .Rows(i).Delete
Next
End Sub

HTH

Charles Chickering

marthasanchez wrote:
How do I delete an enitre row based on one columns number. For example,
if
column A has a 5 then the entire row should be deleted and the formula
move
to the next row.
This is what i have, but it is siftong through columns.

Sub click()
Dim i As Integer
Sheet1.Select
Sheet1.Range("a2:b5").Select
For i = 2 To 6
Cells(2, i).Select
If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete
Next i
End Sub

PLEASE HELP

Delete entire row with a positive number
 

Martha, Assuming that you have a Column in which you KNOW has data in
the last used row...
Dim LastRow As Long
With Sheets("Sheet1")
LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
End With

Charles
marthasanchez wrote:
Thank-you for the awesome feedback. I have read that the best way to sifr
through the data is from the end of the recordset to the beggining. How do I
do this if the number in the recordset changes each time the program is run?

"Tom Ogilvy" wrote:

also Don's had no period in front of the cells or rows, so it would leave the
impression of working on Sheet1, but actually worked on the activesheet or if
in a sheet module, on the sheet containing the code. No problem if the
activesheet is sheet1 or sheet1 contains the code, but then you don't need
the with.

--
Regards,
Tom Ogilvy


"Die_Another_Day" wrote:

Close, yours would error out as you forgot the next statement, although
that wasn't my point for posting, I was trying to show th OP what was
wrong with her code and how to correct it.
Charles
Don Guillett wrote:
Isn't that the same as my post?

--
Don Guillett
SalesAid Software

"Die_Another_Day" wrote in message
oups.com...
Martha, the main problem is that as you delete a row, you end up
skipping the next row, for example if B2 = 5, and B3 = 5, first you
delete row 2, now B2 holds the value that was in B3 and you said to go
to "Next i" which means the original value from B3 never gets checked.
Also the syntax for the Cells object is backwards, Cells(2,i) where i =
6 refers to Row 2, Column 6. The best approach to deleting rows is to
move backwards through them:
Sub click()
Dim i as Integer
With Sheet1
For i = 6 to 2 Step -1
If .Range("B" & i) 0 Then .Rows(i).Delete
Next
End Sub

HTH

Charles Chickering

marthasanchez wrote:
How do I delete an enitre row based on one columns number. For example,
if
column A has a 5 then the entire row should be deleted and the formula
move
to the next row.
This is what i have, but it is siftong through columns.

Sub click()
Dim i As Integer
Sheet1.Select
Sheet1.Range("a2:b5").Select
For i = 2 To 6
Cells(2, i).Select
If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete
Next i
End Sub

PLEASE HELP

Delete entire row with a positive number
 

OK...dont mean to sound slow....but, so how would i merge this code and the
previous code into one?
Dim LastRow As Long
Dim i as interger
With Sheets("Sheet1")
LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
cells (i,2).select
if sheet1.cells (i,2).0 then sheet1.rows (i).delete?
End With
exit sub?






"Die_Another_Day" wrote:

Martha, Assuming that you have a Column in which you KNOW has data in
the last used row...
Dim LastRow As Long
With Sheets("Sheet1")
LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
End With

Charles
marthasanchez wrote:
Thank-you for the awesome feedback. I have read that the best way to sifr
through the data is from the end of the recordset to the beggining. How do I
do this if the number in the recordset changes each time the program is run?

"Tom Ogilvy" wrote:

also Don's had no period in front of the cells or rows, so it would leave the
impression of working on Sheet1, but actually worked on the activesheet or if
in a sheet module, on the sheet containing the code. No problem if the
activesheet is sheet1 or sheet1 contains the code, but then you don't need
the with.

--
Regards,
Tom Ogilvy


"Die_Another_Day" wrote:

Close, yours would error out as you forgot the next statement, although
that wasn't my point for posting, I was trying to show th OP what was
wrong with her code and how to correct it.
Charles
Don Guillett wrote:
Isn't that the same as my post?

--
Don Guillett
SalesAid Software

"Die_Another_Day" wrote in message
oups.com...
Martha, the main problem is that as you delete a row, you end up
skipping the next row, for example if B2 = 5, and B3 = 5, first you
delete row 2, now B2 holds the value that was in B3 and you said to go
to "Next i" which means the original value from B3 never gets checked.
Also the syntax for the Cells object is backwards, Cells(2,i) where i =
6 refers to Row 2, Column 6. The best approach to deleting rows is to
move backwards through them:
Sub click()
Dim i as Integer
With Sheet1
For i = 6 to 2 Step -1
If .Range("B" & i) 0 Then .Rows(i).Delete
Next
End Sub

HTH

Charles Chickering

marthasanchez wrote:
How do I delete an enitre row based on one columns number. For example,
if
column A has a 5 then the entire row should be deleted and the formula
move
to the next row.
This is what i have, but it is siftong through columns.

Sub click()
Dim i As Integer
Sheet1.Select
Sheet1.Range("a2:b5").Select
For i = 2 To 6
Cells(2, i).Select
If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete
Next i
End Sub

PLEASE HELP

Delete entire row with a positive number
 

Sub click()
Dim cnt as Long
Dim LastRow As Long
With Sheet1
LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
For cnt = LastRow to 2 Step -1
If .Range("B" & cnt) 0 Then .Rows(i).Delete
Next
End With
End Sub

That should get you going. Let me know if you need more help.

Charles

marthasanchez wrote:
OK...dont mean to sound slow....but, so how would i merge this code and the
previous code into one?
Dim LastRow As Long
Dim i as interger
With Sheets("Sheet1")
LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
cells (i,2).select
if sheet1.cells (i,2).0 then sheet1.rows (i).delete?
End With
exit sub?






"Die_Another_Day" wrote:

Martha, Assuming that you have a Column in which you KNOW has data in
the last used row...
Dim LastRow As Long
With Sheets("Sheet1")
LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
End With

Charles
marthasanchez wrote:
Thank-you for the awesome feedback. I have read that the best way to sifr
through the data is from the end of the recordset to the beggining. How do I
do this if the number in the recordset changes each time the program is run?

"Tom Ogilvy" wrote:

also Don's had no period in front of the cells or rows, so it would leave the
impression of working on Sheet1, but actually worked on the activesheet or if
in a sheet module, on the sheet containing the code. No problem if the
activesheet is sheet1 or sheet1 contains the code, but then you don't need
the with.

--
Regards,
Tom Ogilvy


"Die_Another_Day" wrote:

Close, yours would error out as you forgot the next statement, although
that wasn't my point for posting, I was trying to show th OP what was
wrong with her code and how to correct it.
Charles
Don Guillett wrote:
Isn't that the same as my post?

--
Don Guillett
SalesAid Software

"Die_Another_Day" wrote in message
oups.com...
Martha, the main problem is that as you delete a row, you end up
skipping the next row, for example if B2 = 5, and B3 = 5, first you
delete row 2, now B2 holds the value that was in B3 and you said to go
to "Next i" which means the original value from B3 never gets checked.
Also the syntax for the Cells object is backwards, Cells(2,i) where i =
6 refers to Row 2, Column 6. The best approach to deleting rows is to
move backwards through them:
Sub click()
Dim i as Integer
With Sheet1
For i = 6 to 2 Step -1
If .Range("B" & i) 0 Then .Rows(i).Delete
Next
End Sub

HTH

Charles Chickering

marthasanchez wrote:
How do I delete an enitre row based on one columns number. For example,
if
column A has a 5 then the entire row should be deleted and the formula
move
to the next row.
This is what i have, but it is siftong through columns.

Sub click()
Dim i As Integer
Sheet1.Select
Sheet1.Range("a2:b5").Select
For i = 2 To 6
Cells(2, i).Select
If Sheet1.Cells(2, i) 0 Then Sheet1.Rows(i).Delete
Next i
End Sub

PLEASE HELP