The deleting of Rows will cause a problem with the For loop.

Not if you use a For loop that loops from the end of the columns of data
upward to the beginning of the data (using a Step -1 statement to effect the
backward iteration). For example...

For X = LastRow To StartRow Step -1

--
Rick (MVP - Excel)


"Joel" wrote in message
...
when deleting rows use a Do Loop, not a for loop. The deleting of Rows
will
cause a problem with the For loop.

Dim lRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False

set c = lRow = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious)
if not c is nothing then
LastRow = c

RowCount = LastRow
Do while RowCount = 1


If WorksheetFunction.CountA(Rows(Rowcount)) = 0 Then
Rows(RowCount).Delete
end if
RowCount = RowCount - 1
Loop

.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True

end with


range("A2:A300").ClearContents
range("A2").FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",TODAY())"
range("A2").AutoFill _
Destination:=range("A2:A300"), _
Type:=xlFillDefault


"puiuluipui" wrote:

Hi, i have in a macro this part of the code that delete empty rows:

Dim lRow As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
For lRow = Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious).Row To 1 Step -1
If WorksheetFunction.CountA(Rows(lRow)) = 0 Then Rows(lRow).Delete
Next lRow
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True

and then this one:

range("A2:A300").Select
Selection.ClearContents
range("A2").Select

range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",TODAY())"
Selection.AutoFill Destination:=range("A2:A300"), Type:=xlFillDefault
range("A2:A300").Select
range("A2").Select
The problem is that the second part is blocking the first one. i neet to
delete empty rows and then to add formula.
What am i doing wrong?
Thanks!