Input Box Cancel Event

C

Craig

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.
 
N

Nikos Yannacopoulos

Craig,

I suspect the problem is in your condition:
If ActiveCell.Value = "" Then
Click to expand...

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
 
J

jacob

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
 
D

Don Lloyd

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
 
Top