Stop Loop
 

Hi,

I have the following VBA that deletes all empty rows within a range of
data. The only problem I am facing is that the loop keeps running,
hence it looks like it does not stop after row 4500.
Can someone tell me which command I have to define to stop the Looping
after row 4500?

Worksheets("Compiled").Activate
Range("A1:A4500").Select
Dim rng As Range
Set rng = Columns(1).Find("")
If Not rng Is Nothing Then
Do
rng.EntireRow.Delete
Set rng = Columns(1).Find("")
Loop While Not rng Is Nothing
End If



Thanks a lot for your help!

Rgds,
Robert

Stop Loop
 

Hi Mike

Thanks for your advice!

Perhaps you can also help me with another issue. I compiled a certain
data range based on formulas that refere to a set of raw data. As the
raw data set varies from time to time I extended the formulas with an
IF function (=IF(ISNUMBER(A1),A1,""))) in anticipation of new data
that might be added in the future. After that I copy the compiled data
range and paste it in a new sheet using a macro. Although I paste the
range as "Paste Special" it somehow still interprets the empty fields
as fields with a content, hence when I want to past the following range
below it leaves me with a few empty lines in between.
Are you aware of a remedy against this problem?

Thanks again.

Rgds,
Robert




Mike schreef:
I'm sure one of our MVP's will come up with a much better solution but until
then this works for me.

Worksheets("Compiled").Activate
Range("A1:A4500").Select
Dim rng As Range
Set rng = Columns(1).Find("")
If Not rng Is Nothing Then
Do
rng.EntireRow.Delete
Set rng = Columns(1).Find("")
x = x + 1
Loop Until x = 4501
End If

Mike


"Robert" wrote:

Hi,

I have the following VBA that deletes all empty rows within a range of
data. The only problem I am facing is that the loop keeps running,
hence it looks like it does not stop after row 4500.
Can someone tell me which command I have to define to stop the Looping
after row 4500?

Worksheets("Compiled").Activate
Range("A1:A4500").Select
Dim rng As Range
Set rng = Columns(1).Find("")
If Not rng Is Nothing Then
Do
rng.EntireRow.Delete
Set rng = Columns(1).Find("")
Loop While Not rng Is Nothing
End If



Thanks a lot for your help!

Rgds,
Robert

Stop Loop
 

Your code inefficiently looks for rows to delete, and has no mechanism to
stop at any particular row.

Here's a better way to delete rows with blanks in column A. It only examines
the used range of the sheet, and waits until the end to delete all rows at
once.

Sub Delete_Empty_Rows_1()
On Error Resume Next
Dim currentCell As Excel.Range, deleteRange As Excel.Range
For Each currentCell In Application.Intersect _
(ActiveSheet.UsedRange, ActiveSheet.Columns(1))
If currentCell = vbNullString Then
If Not deleteRange Is Nothing Then
Set deleteRange = Application.Union _
(deleteRange, currentCell.EntireRow)
Else
Set deleteRange = currentCell.EntireRow
End If
End If
Next currentCell
deleteRange.Delete
End Sub

The following deletes rows as it finds them, but it starts from the end and
works its way upward:

Public Sub DeleteBlankRows()
'Chip Pearson

Dim R As Long
Dim C As Range
Dim N As Long
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
N = 0
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).C olumns(1)) = 0
Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
Application.StatusBar = "Row: " + Format(R)
Next R

EndMacro:

Application.StatusBar = "Rows Deleted: " + Format(N)
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Mike" wrote in message
...
I'm sure one of our MVP's will come up with a much better solution but
until
then this works for me.

Worksheets("Compiled").Activate
Range("A1:A4500").Select
Dim rng As Range
Set rng = Columns(1).Find("")
If Not rng Is Nothing Then
Do
rng.EntireRow.Delete
Set rng = Columns(1).Find("")
x = x + 1
Loop Until x = 4501
End If

Mike


"Robert" wrote:

Hi,

I have the following VBA that deletes all empty rows within a range of
data. The only problem I am facing is that the loop keeps running,
hence it looks like it does not stop after row 4500.
Can someone tell me which command I have to define to stop the Looping
after row 4500?

Worksheets("Compiled").Activate
Range("A1:A4500").Select
Dim rng As Range
Set rng = Columns(1).Find("")
If Not rng Is Nothing Then
Do
rng.EntireRow.Delete
Set rng = Columns(1).Find("")
Loop While Not rng Is Nothing
End If



Thanks a lot for your help!

Rgds,
Robert