Runtime error in Macro.

[Tel]

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

 

[Don Guillett]

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

 

[Tel]

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

 

[Luke M]

As it was written, yes, it would have applied to all of column E. You can
change that part to just a range callout, however. And yes, it deletes ALL
rows with blanks. Your complete macro, including the sheet copy, would be
this, I believe:

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


Sheets("Remediation
Plan").Range("E4:E34").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[Don Guillett]

This REPLACES all of your code. I went back and did see that you were
copying a sheet. However, I did not see that you pasted it anywhere. This
will copy your sheet to a new workbook, delete the blank rows in c4:c34 and
save the sheet as a new workbook.

Sub delifEblank()
Sheets("Redemption Plan").Copy
Cells(4, "e").Resize(31).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveWorkbook.Save
End Sub