Macro problem
 

I am trying to write a macro which would:
1) check the cell to the left - if value is 0 (or cell is empty) then macro
is abandoned else......
2) if current cell is non-empty then the row is deleted and back to 1) to
check the next row
3) if current cell is empty then move down 1 cell and back to 1)

I have tried the following:
Sub DeleteRow_If_Cell_NotEmpty()
Do
If ActiveCell.Offset(0, -1).Value 0 Then
If ActiveCell < "" Then
Selection.EntireRow.Delete
Else 'go to next row
ActiveCell.Offset(1, 0).Select
End If
Else
Exit Sub
End If

Loop
End Sub

If I step into the macro using F8 in VB editor it works fine. But when I
use the macro normally I get the error "Code execution has been interrupted"
and on clicking the debug button the first End If above is highlighted.

Would appreciate your help.

Al

Macro problem
 

Sub DeleteRow_If_Cell_NotEmpty()
Dim iLastRow As Long
Dim iCol As Long
Dim i As Long

iCol = ActiveCell.Column

With ActiveSheet
iLastRow = .Cells(.Rows.Count, iCol).End(xlUp).Row

For i = iLastRow To ActiveCell.Row Step -1
If .Cells(i, iCol - 1).Value < 0 Then
If .Cells(i, iCol).Value < "" Then
.Rows(i).Delete
End If
Else
Exit Sub
End If

Next i
End With
End Sub



--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"PE" wrote in message
...
I am trying to write a macro which would:
1) check the cell to the left - if value is 0 (or cell is empty) then
macro
is abandoned else......
2) if current cell is non-empty then the row is deleted and back to 1) to
check the next row
3) if current cell is empty then move down 1 cell and back to 1)

I have tried the following:
Sub DeleteRow_If_Cell_NotEmpty()
Do
If ActiveCell.Offset(0, -1).Value 0 Then
If ActiveCell < "" Then
Selection.EntireRow.Delete
Else 'go to next row
ActiveCell.Offset(1, 0).Select
End If
Else
Exit Sub
End If

Loop
End Sub

If I step into the macro using F8 in VB editor it works fine. But when I
use the macro normally I get the error "Code execution has been
interrupted"
and on clicking the debug button the first End If above is highlighted.

Would appreciate your help.

Al

Macro problem
 

Your code ran fine for me (functionally I can't say, but it didn't error).
Do you have any merged cells in the column you are processing?

--
Regards,
Tom Ogilvy



"PE" wrote:

I am trying to write a macro which would:
1) check the cell to the left - if value is 0 (or cell is empty) then macro
is abandoned else......
2) if current cell is non-empty then the row is deleted and back to 1) to
check the next row
3) if current cell is empty then move down 1 cell and back to 1)

I have tried the following:
Sub DeleteRow_If_Cell_NotEmpty()
Do
If ActiveCell.Offset(0, -1).Value 0 Then
If ActiveCell < "" Then
Selection.EntireRow.Delete
Else 'go to next row
ActiveCell.Offset(1, 0).Select
End If
Else
Exit Sub
End If

Loop
End Sub

If I step into the macro using F8 in VB editor it works fine. But when I
use the macro normally I get the error "Code execution has been interrupted"
and on clicking the debug button the first End If above is highlighted.

Would appreciate your help.

Al

Macro problem
 

Thank you for the code below.

Unfortunately the same thing happens with this when I run the macro, i.e.,
after deleting a few rows (varies each time) I get the message "Code
execution has been interrupted" and I am given the choice to Continue,
Cancel, Debug, Help. If I continue a few more rows are deleted and the same
thing happens!

Something is causing the code to stop running (always stops with the first
End If highlighted) - clicking Help button I get the following:

Code execution has been interrupted
Code execution can be suspended when necessary. This condition has the
following cause and solution:

a.. A CTRL+BREAK (Microsoft Windows), ESC (Microsoft Excel) or
COMMAND+PERIOD (Macintosh) key combination has been encountered.
In the error dialog box, click Debug to enter break mode, Continue to
resume, or End to stop execution.

For additional information, select the item in question and press F1 (in
Windows) or HELP (on the Macintosh).

Any idea what may be causing this?

Al

"Bob Phillips" wrote in message
...
Sub DeleteRow_If_Cell_NotEmpty()
Dim iLastRow As Long
Dim iCol As Long
Dim i As Long

iCol = ActiveCell.Column

With ActiveSheet
iLastRow = .Cells(.Rows.Count, iCol).End(xlUp).Row

For i = iLastRow To ActiveCell.Row Step -1
If .Cells(i, iCol - 1).Value < 0 Then
If .Cells(i, iCol).Value < "" Then
.Rows(i).Delete
End If ------------------------- This line is
highlighted when code interrupted ----------------------------
Else
Exit Sub
End If

Next i
End With
End Sub



--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"PE" wrote in message
...
I am trying to write a macro which would:
1) check the cell to the left - if value is 0 (or cell is empty) then
macro
is abandoned else......
2) if current cell is non-empty then the row is deleted and back to 1) to
check the next row
3) if current cell is empty then move down 1 cell and back to 1)

I have tried the following:
Sub DeleteRow_If_Cell_NotEmpty()
Do
If ActiveCell.Offset(0, -1).Value 0 Then
If ActiveCell < "" Then
Selection.EntireRow.Delete
Else 'go to next row
ActiveCell.Offset(1, 0).Select
End If
Else
Exit Sub
End If

Loop
End Sub

If I step into the macro using F8 in VB editor it works fine. But when I
use the macro normally I get the error "Code execution has been
interrupted"
and on clicking the debug button the first End If above is highlighted.

Would appreciate your help.

Al

Macro problem
 

There are no merged cells in the column but the top cell in the column is
merged with four on the left.

I have unmerged all the cells but still the macro gets interrupted.

"Tom Ogilvy" wrote in message
...
Your code ran fine for me (functionally I can't say, but it didn't error).
Do you have any merged cells in the column you are processing?

--
Regards,
Tom Ogilvy



"PE" wrote:

I am trying to write a macro which would:
1) check the cell to the left - if value is 0 (or cell is empty) then
macro
is abandoned else......
2) if current cell is non-empty then the row is deleted and back to 1) to
check the next row
3) if current cell is empty then move down 1 cell and back to 1)

I have tried the following:
Sub DeleteRow_If_Cell_NotEmpty()
Do
If ActiveCell.Offset(0, -1).Value 0 Then
If ActiveCell < "" Then
Selection.EntireRow.Delete
Else 'go to next row
ActiveCell.Offset(1, 0).Select
End If
Else
Exit Sub
End If

Loop
End Sub

If I step into the macro using F8 in VB editor it works fine. But when I
use the macro normally I get the error "Code execution has been
interrupted"
and on clicking the debug button the first End If above is highlighted.

Would appreciate your help.

Al