Hi Don,

Many thanks for your reply. would this only apply if we were looking at all
of Col E as I have a blank cell in E2 which forms part of my "header"? Also,
look at e4, and act then e5 and act then e6 etc?

Finally, I'm guessing it would go after the "Copy" element of the overall
code?

Sorry if I appear a bit of a numpty but I've been trying to sort this since
the weekend.

Tel

"Don Guillett" wrote:

If all you want to do is delete rows if col E is blank then this ONE liner
should do it.

Sub delifColEblank()
Sheets("Remediation
Plan").Columns("E").SpecialCells(xlCellTypeBlanks) .EntireRow.Delete
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tel" wrote in message
...
Hi All,

Just when I thought it was safe.

I've got a problem with a Macro. What I'm seeking to do is create a copy
of
the plan "Remediation Plan" and then, if the cell in "E" is blank then
delete the entire row and then check. The full range is from Cell E4 to
E34.

I've copied Ron DeBruin's code and it was working but now I get an error
stating: "Run-time error '1004'"

"Delete method of Range class failed"

When I run "debug" it is the row stating "FoundCell.EntireRow.Delete"
(marked with a * on my code below - the * isn't part of the code) which is
highlighted.

The idea is to give the user a shortened version of the worksheet whereby
if
the cell in column "E" is blank they don't need it.


Sub Copy_RM_Plan()
'
' Copy_RM_Plan Macro
' Macro recorded 26/06/2009 by Terry B Glover
'

'
Sheets("Remediation Plan").Select
Application.CutCopyMode = False
Sheets("Remediation Plan").Copy

Dim calcmode As Long
Dim ViewMode As Long
Dim myStrings As Variant
Dim FoundCell As Range
Dim I As Long
Dim myRng As Range
Dim sh As Worksheet

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'We use the ActiveSheet but you can also use Sheets("MySheet")
Set sh = ActiveSheet

'We search in column A in this example
Set myRng = sh.Range("E4:E34")

'Add more search strings if you need
myStrings = Array("")


With sh

'We select the sheet so we can change the window view
.Select

'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False

'We will search the values in MyRng in this example
With myRng

For I = LBound(myStrings) To UBound(myStrings)
Do
Set FoundCell = myRng.Find(What:=myStrings(I), _
After:=.Cells(.Cells.Count),
_
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
'Use xlPart If you want to search in a part of the
FoundCell
'If you use LookIn:=xlValues it will also delete rows
with a
'formula that evaluates to "Ron"
If FoundCell Is Nothing Then
Exit Do
Else
* FoundCell.EntireRow.Delete
End If
Loop
Next I

End With

End With

ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = calcmode
End With


End Sub