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

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

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

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(xlCellTypeBlan ks).EntireRow.Delete
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Tel" wrote:

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

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).Ent ireRow.Delete
ActiveWorkbook.Save
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Tel" wrote in message
...
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