Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box Cancel Event
I'm using Excel XP. I can't seem to get the following
code to work. The following code deletes any blank row, and then moves to the next row and checks if it's blank, too. If it is, the row is deleted. If not, the row is skipped, etc. The problem I run into is that after the text that I want has had all the blank rows removed, the code will continue executing indefinitely. I need the code to STOP after it has completed deleting the blank rows from my block of text. What I'm trying to do below is set a counter ('y') so that if 25 consecutive rows are blank, then the code will know that the end of the text has come and it's time to end the macro. But the line "y = y + 1" generates the error "code execution has been interrupted." Any thoughts? Sub DeleteRows() Dim y As Long y = 0 Do While y < 25 If ActiveCell.Value = "" Then Selection.EntireRow.Delete y = y + 1 Else ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate y = 0 End If Loop End Sub Thanks. C. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box Cancel Event
Craig,
I suspect the problem is in your condition: If ActiveCell.Value = "" Then which will NOT be satisfied in an empty cell, so the True part will never be executed! Try using the following instead: If IsEmpty(ActiveCell) Or ActiveCell.Value = "" Then I have included both because I believe your 'blank' cells in between the data are indeed zero length strings (""), otherwise your code wouldn't remove any rows as is. Including both conditions with an OR should do it. Yet, this is not necessarily the most efficient way to do it. I would start from the end working up, either going to the last used cell via: ActiveCell.SpecialCells(xlLastCell).Select ActiveCell.Offset(0,1-ActiveCell.Column).Select (the second line will take you to column A in the last row) or, by going to the very last row of the sheet (65536) and then finding the first non-empty cell up: Range("A65536").Select ActiveCell.End(xlUp).Select Then, I would work my way up in the same manner as you, stopping when ActiveCell.Row =1. Another way to make it faster would be to keep your ActiveCell constant throughout, and work with the Offset (just remember to not advance the Offset when a row is deleted). HTH, Nikos "Craig" wrote in message ... I'm using Excel XP. I can't seem to get the following code to work. The following code deletes any blank row, and then moves to the next row and checks if it's blank, too. If it is, the row is deleted. If not, the row is skipped, etc. The problem I run into is that after the text that I want has had all the blank rows removed, the code will continue executing indefinitely. I need the code to STOP after it has completed deleting the blank rows from my block of text. What I'm trying to do below is set a counter ('y') so that if 25 consecutive rows are blank, then the code will know that the end of the text has come and it's time to end the macro. But the line "y = y + 1" generates the error "code execution has been interrupted." Any thoughts? Sub DeleteRows() Dim y As Long y = 0 Do While y < 25 If ActiveCell.Value = "" Then Selection.EntireRow.Delete y = y + 1 Else ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate y = 0 End If Loop End Sub Thanks. C. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box Cancel Event
Hi,
If you use the below function (from Walkenbach's book) it will give you the row number for the last row which have any content (checking only the first column of rng). You could then loop a definite number of times. Function LastRow(rng As Range) As Long Dim LastCell As Range Set LastCell = rng.Parent.Cells(Rows.Count, rng.Column).End(xlUp) LastRow = LastCell.Row If rng.Parent.Cells(Rows.Count, rng.Column) < "" Then LastRow = Rows.Count End Function jacob -----Original Message----- I'm using Excel XP. I can't seem to get the following code to work. The following code deletes any blank row, and then moves to the next row and checks if it's blank, too. If it is, the row is deleted. If not, the row is skipped, etc. The problem I run into is that after the text that I want has had all the blank rows removed, the code will continue executing indefinitely. I need the code to STOP after it has completed deleting the blank rows from my block of text. What I'm trying to do below is set a counter ('y') so that if 25 consecutive rows are blank, then the code will know that the end of the text has come and it's time to end the macro. But the line "y = y + 1" generates the error "code execution has been interrupted." Any thoughts? Sub DeleteRows() Dim y As Long y = 0 Do While y < 25 If ActiveCell.Value = "" Then Selection.EntireRow.Delete y = y + 1 Else ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate y = 0 End If Loop End Sub Thanks. C. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input Box Cancel Event
Hi Craig,
When you delete a row, the row numbers of items below all change, so the "Last Row" keeps changing. Starting from the bottom up overcomes this.The following uses the Column of the Activecell to determine the last occupied cell and works from there up. Note that there is no need to Select or Activate. Sub DelRows() Dim LastRow, Rw, Col Col = ActiveCell.Column LastRow = Cells(Rows.Count, Col).End(xlUp).Row For Rw = LastRow To 1 Step -1 If Cells(Rw, Col) = "" Then Rows(Rw).Delete End If Next End Sub Depending on your circumstances, it may be prudent to specify the column directly rather than rely on the selected cell. e.g. Col = 4 Results could be wierd if a cell in the wrong column was selected regards, Don "Craig" wrote in message ... I'm using Excel XP. I can't seem to get the following code to work. The following code deletes any blank row, and then moves to the next row and checks if it's blank, too. If it is, the row is deleted. If not, the row is skipped, etc. The problem I run into is that after the text that I want has had all the blank rows removed, the code will continue executing indefinitely. I need the code to STOP after it has completed deleting the blank rows from my block of text. What I'm trying to do below is set a counter ('y') so that if 25 consecutive rows are blank, then the code will know that the end of the text has come and it's time to end the macro. But the line "y = y + 1" generates the error "code execution has been interrupted." Any thoughts? Sub DeleteRows() Dim y As Long y = 0 Do While y < 25 If ActiveCell.Value = "" Then Selection.EntireRow.Delete y = y + 1 Else ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate y = 0 End If Loop End Sub Thanks. C. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot seem to Cancel EXCEL App Right Click Event using C# | Excel Programming | |||
Input Box Cancel | Excel Discussion (Misc queries) | |||
Cancel sheet change event | Charts and Charting in Excel | |||
cancel input | Excel Discussion (Misc queries) | |||
Cancel terminate event?? | Excel Programming |