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