Deleting blank rows in a range or another fill approach

Alright - I've moved the formulas to a second sheet (that I'll later hide) so that everything is nice and neat, starting in column A. The formulas are only in cells A2:A21 for testing.

Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
StartRow = 2
EndRow = 21
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "a").Value) Then
'Do nothing, This avoid a error if there is a error in the cell

ElseIf IsText(.Cells(Lrow, "a").Value) Then .Rows(Lrow).Delete
'This will delete the row if the cell is empty

End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With


End Sub

Column A is filled with these formulas - =IF(F2=" ","",Sheet2!$D$4). When I run the code, I get nothing. Ideas?

Here is the other code -

Sub DeleteBlankRows()

End Sub
Dim rownum As Integer
rownum = 2

Do Until rownum = 21

cellloc = "A" & 2

Range(cellloc).Select
If Range(cellloc).Value = "" Then
Rows(rownum).Select
Selection.Delete Shift:=xlUp
Else
End If

rownum = rownum + 1
Loop

Range("A1").Select

End Sub

When I ran this code, it delted the columns between the first and next cells with text, but none of the others (that contain forumlas). ???

Thanks

Use

ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete


--
Regards Ron de Bruin
http://www.rondebruin.nl



"BigDave" <BigDave.1q0rqi_1117749914.3626@excelforum-nospam.com> wrote in message
news:BigDave.1q0rqi_1117749914.3626@excelforum-nospam.com...
>
> Alright - I've moved the formulas to a second sheet (that I'll later
> hide) so that everything is nice and neat, starting in column A. The
> formulas are only in cells A2:A21 for testing.
>
>>
>> Dim Lrow As Long
>> Dim CalcMode As Long
>> Dim StartRow As Long
>> Dim EndRow As Long
>> With Application
>> CalcMode = .Calculation
>> .Calculation = xlCalculationManual
>> .ScreenUpdating = False
>> End With
>>
>> With ActiveSheet
>> .DisplayPageBreaks = False
>> StartRow = 2
>> EndRow = 21
>> For Lrow = EndRow To StartRow Step -1
>> If IsError(.Cells(Lrow, "a").Value) Then
>> 'Do nothing, This avoid a error if there is a error in the cell
>>
>> ElseIf IsText(.Cells(Lrow, "a").Value) Then .Rows(Lrow).Delete
>> 'This will delete the row if the cell is empty
>>
>> End If
>> Next
>> End With
>> With Application
>> .ScreenUpdating = True
>> .Calculation = CalcMode
>> End With
>>
>>
>> End Sub
>
> Column A is filled with these formulas - *=IF(F2=" ","",Sheet2!$D$4)*.
> When I run the code, I get nothing. Ideas?
>
> Here is the other code -
>>
>> Sub DeleteBlankRows()
>>
>> End Sub
>> Dim rownum As Integer
>> rownum = 2
>>
>> Do Until rownum = 21
>>
>> cellloc = "A" & 2
>>
>> Range(cellloc).Select
>> If Range(cellloc).Value = "" Then
>> Rows(rownum).Select
>> Selection.Delete Shift:=xlUp
>> Else
>> End If
>>
>> rownum = rownum + 1
>> Loop
>>
>> Range("A1").Select
>>
>> End Sub
>
> When I ran this code, it delted the columns between the first and next
> cells with text, but none of the others (that contain forumlas). ???
>
> Thanks
>
>
> --
> BigDave
> ------------------------------------------------------------------------
> BigDave's Profile: http://www.excelforum.com/member.php...fo&userid=7741
> View this thread: http://www.excelforum.com/showthread...hreadid=376074
>