Macro to Delete Row

[Guest]

The following code does not seem to work in Excel 2000. It worked in Excel
XP. I need to evaluate a cell, starting at D2, and then delete the row if
the cell is empty stopping at the last row. The column being evaluated
contains dates so if the item is not closed the cell is empty.

Dim TestColumn As Long
Dim cRows As Long
Dim i As Long

TestColumn = 1
cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row
Range("D2").Select

For i = 2 To cRows
If ActiveCell.Value > 0 Then Selection.EntireRow.Delete
Next i

 

[Fredrik Wahlgren]

The following code does not seem to work in Excel 2000. It worked in Excel
XP. I need to evaluate a cell, starting at D2, and then delete the row if
the cell is empty stopping at the last row. The column being evaluated
contains dates so if the item is not closed the cell is empty.

Dim TestColumn As Long
Dim cRows As Long
Dim i As Long

TestColumn = 1
cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row
Range("D2").Select

For i = 2 To cRows
If ActiveCell.Value > 0 Then Selection.EntireRow.Delete
Next i

Wher does your code fail?

try somthing like

On Error GoTo ErrorHandler
Dim TestColumn As Long
Dim cRows As Long
Dim i As Long

TestColumn = 1
cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row
Range("D2").Select

For i = 2 To cRows
If ActiveCell.Value > 0 Then Selection.EntireRow.Delete
Next i
exit Sub

ErrorHandler
MsgBox Err.Message

/Fredrik

 

[Guest]

It acutally looks like it completes all steps of the macro, but no lines are
deleted. I have tried stepping through the macro and it goes through the
step but nothing happens and I do not get an error. It is as if the For/Next
loop does not do anything!

What would be the code to display the current value of cRows during the loop
so I can see if anything is actually happening?

Is the cRows= statement correct?

Thanks

 

[Fredrik Wahlgren]

It acutally looks like it completes all steps of the macro, but no lines are
deleted. I have tried stepping through the macro and it goes through the
step but nothing happens and I do not get an error. It is as if the For/Next
loop does not do anything!

What would be the code to display the current value of cRows during the loop
so I can see if anything is actually happening?

Is the cRows= statement correct?

Thanks

I haven't examined your code. If it's a Sub, use MsgBox. You can also step
thru tehe code, the debugger should show the value of cRows.

/Fredrik

/Fredrik

 

[KL]

Tom,

If I understand you correctly then you may consider the following code.
Also, I very much doubt the code you show could work correctly in any
version of Excel (unless I misundrestood the task)

Regards,
KL

Sub DeleteRows()
Dim cRows As Single
Dim i As Single
cRows = Cells(Rows.Count, 1).End(xlUp).Row
For i = cRows To 2 Step -1
If Cells(i, 4) > 0 Then Rows(i).Delete
Next i
End Sub

 

[Tim Coddington]

You are examining the same cell over-and-over.
You could put in an activecell.offset(1,0) to move to the next cell.

 

[Guest]

Thanks all. I finally figured it out. For cRows =..., the column that it
was using to count contained no entries, so the count was zero!!!!! This
code waorks because I sort prior to deleting so that all rows that need to be
deleted are together. When it encounters the first row with a blank cell, it
sits there until finishing the loop. I then have it resort by another column.

Tom,

If I understand you correctly then you may consider the following code.
Also, I very much doubt the code you show could work correctly in any
version of Excel (unless I misundrestood the task)

Regards,
KL

Sub DeleteRows()
Dim cRows As Single
Dim i As Single
cRows = Cells(Rows.Count, 1).End(xlUp).Row
For i = cRows To 2 Step -1
If Cells(i, 4) > 0 Then Rows(i).Delete
Next i
End Sub