Delete row problem, could some help me on that?
 

Hello guys,

I have a problem with this small sub, I would like to delete the row it
the value of one cell is egual of the value of cell + 1 but I have this
problem delete method of range class failed and I do not know why?

Sub deletequalrow()
Dim LastRow As Long
Dim i As Long


With ActiveSheet.UsedRange
LastRow = .Cells(.Cells.Count).Row
End With


For i = LastRow To 1 Step -1
If Cells(i, "B").Value = Cells(i - 1, "B").Value Then
Rows(i).EntireRow.Delete
End If
Next i
End Sub

Ina

Delete row problem, could some help me on that?
 

For one thing, when i = 1, there is no cell i-1, "B". Try
For i=LastRow to 2 step -1
James
ina wrote:
Hello guys,

I have a problem with this small sub, I would like to delete the row it
the value of one cell is egual of the value of cell + 1 but I have this
problem delete method of range class failed and I do not know why?

Sub deletequalrow()
Dim LastRow As Long
Dim i As Long


With ActiveSheet.UsedRange
LastRow = .Cells(.Cells.Count).Row
End With


For i = LastRow To 1 Step -1
If Cells(i, "B").Value = Cells(i - 1, "B").Value Then
Rows(i).EntireRow.Delete
End If
Next i
End Sub

Ina

Delete row problem, could some help me on that?
 

The Cell procedu Cells(i, "B").Value is wrong, the first parameter is a
row index which you have, but the second is not a string it should be the
number 2 if you want to use column B

"ina" wrote:

Hello guys,

I have a problem with this small sub, I would like to delete the row it
the value of one cell is egual of the value of cell + 1 but I have this
problem delete method of range class failed and I do not know why?

Sub deletequalrow()
Dim LastRow As Long
Dim i As Long


With ActiveSheet.UsedRange
LastRow = .Cells(.Cells.Count).Row
End With


For i = LastRow To 1 Step -1
If Cells(i, "B").Value = Cells(i - 1, "B").Value Then
Rows(i).EntireRow.Delete
End If
Next i
End Sub

Ina

Delete row problem, could some help me on that?
 

You're going from the lastrow to 1 step -1

So the last thing you're doing is comparing

if cells(1,"B").value = cells(0,"B").value then
(1-1 is 0)

Try changing your loop to stop at row 2.

For i = LastRow To 2 Step -1



ina wrote:

Hello guys,

I have a problem with this small sub, I would like to delete the row it
the value of one cell is egual of the value of cell + 1 but I have this
problem delete method of range class failed and I do not know why?

Sub deletequalrow()
Dim LastRow As Long
Dim i As Long

With ActiveSheet.UsedRange
LastRow = .Cells(.Cells.Count).Row
End With

For i = LastRow To 1 Step -1
If Cells(i, "B").Value = Cells(i - 1, "B").Value Then
Rows(i).EntireRow.Delete
End If
Next i
End Sub

Ina

--

Dave Peterson

Delete row problem, could some help me on that?
 

I think you're confusing Range("B13") with cells(13,"B") or cells(13,2)

(I used row 13 just so there wouldn't be too many 2's.)

Michael wrote:

The Cell procedu Cells(i, "B").Value is wrong, the first parameter is a
row index which you have, but the second is not a string it should be the
number 2 if you want to use column B

"ina" wrote:

Hello guys,

I have a problem with this small sub, I would like to delete the row it
the value of one cell is egual of the value of cell + 1 but I have this
problem delete method of range class failed and I do not know why?

Sub deletequalrow()
Dim LastRow As Long
Dim i As Long


With ActiveSheet.UsedRange
LastRow = .Cells(.Cells.Count).Row
End With


For i = LastRow To 1 Step -1
If Cells(i, "B").Value = Cells(i - 1, "B").Value Then
Rows(i).EntireRow.Delete
End If
Next i
End Sub

Ina



--

Dave Peterson

Delete row problem, could some help me on that?
 

Thanks a lot guys it works now.

Ina
Michael wrote:
The Cell procedu Cells(i, "B").Value is wrong, the first parameter is a
row index which you have, but the second is not a string it should be the
number 2 if you want to use column B

"ina" wrote:

Hello guys,

I have a problem with this small sub, I would like to delete the row it
the value of one cell is egual of the value of cell + 1 but I have this
problem delete method of range class failed and I do not know why?

Sub deletequalrow()
Dim LastRow As Long
Dim i As Long


With ActiveSheet.UsedRange
LastRow = .Cells(.Cells.Count).Row
End With


For i = LastRow To 1 Step -1
If Cells(i, "B").Value = Cells(i - 1, "B").Value Then
Rows(i).EntireRow.Delete
End If
Next i
End Sub

Ina