Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|