Run-Time Error 1004 No Cells Were Found

Coosa41

New Member
Joined
May 8, 2019
Messages
6
This is my code. When I run it and no row haw the word Period it gives me the error Run-Time Error 1004 no cells were found. Any help would be appreciated.

Rows("4:4").Select
Selection.Delete Shift:=xlUp
Dim A1 As Range
With Range("A1", Range("A" & Rows.Count).End(xlUp))
.Replace "Period ", True, xlWhole, , False, , False, False
For Each Al In .SpecialCells(xlConstants, xlLogical).Areas
Al.Resize(3).EntireRow.Delete
Next Al
End With
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,930
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
   Rows("4:4").Select
   Selection.Delete Shift:=xlUp
   Dim Rng As Range
   If Application.CountIf(Range("A:A"), "Period ") > 0 Then
      With Range("A1", Range("A" & Rows.Count).End(xlUp))
         .Replace "Period ", True, xlWhole, , False, , False, False
         For Each Rng In .SpecialCells(xlConstants, xlLogical).Areas
            Rng.Resize(3).EntireRow.Delete
         Next Rng
      End With
   End If
 

Coosa41

New Member
Joined
May 8, 2019
Messages
6
How about
VBA Code:
   Rows("4:4").Select
   Selection.Delete Shift:=xlUp
   Dim Rng As Range
   If Application.CountIf(Range("A:A"), "Period ") > 0 Then
      With Range("A1", Range("A" & Rows.Count).End(xlUp))
         .Replace "Period ", True, xlWhole, , False, , False, False
         For Each Rng In .SpecialCells(xlConstants, xlLogical).Areas
            Rng.Resize(3).EntireRow.Delete
         Next Rng
      End With
   End If
This code bypassed everything and worked as long as their wasn't a row that contained period in it. It would leave it and cause another error. Here is a copy of the spreadsheet. Thank you for your help.
 

Attachments

  • Eample.JPG
    Eample.JPG
    120.7 KB · Views: 5

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,930
Office Version
  1. 365
Platform
  1. Windows
Sorry, but I don't understand what you are saying.
If you are getting an error, what is the error & what line caused it?
 

Coosa41

New Member
Joined
May 8, 2019
Messages
6

ADVERTISEMENT

Sorry, but I don't understand what you are saying.
If you are getting an error, what is the error & what line caused it?
This is what I am Running
Dim A1 As Range
With Range("A1", Range("A" & Rows.Count).End(xlUp))
.Replace "Period ", True, xlWhole, , False, , False, False
For Each Al In .SpecialCells(xlConstants, xlLogical).Areas
Al.Resize(3).EntireRow.Delete
Next Al
End With
When the worksheet doesn't at the word Period in a row it stops and wants you to debug it. The highlighted line is

For Each Al In .SpecialCells(xlConstants, xlLogical).Areas

In the example below if it doesn't have the word Period it quits running. But if the word Period as in the example above it runs and deletes that row plus the next two rows.
I don't know if I am explaining this correctly but what I want to do is to delete the rows with a Period along with the next two rows but if it is not present in a row I want to go to the next step as below.
 

Attachments

  • 1610719357721.png
    1610719357721.png
    26.9 KB · Views: 1

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,930
Office Version
  1. 365
Platform
  1. Windows
That is not the code I suggested. Did you try my code?
 

Coosa41

New Member
Joined
May 8, 2019
Messages
6
I did try the code you sent. I am sending all of the worksheet for you to see and the two sets of code mine and yours. Note that if the worksheet has a row with the word Period in it it runs the script as it should. But if it is missing it just stops as you can see in the word doc. Again thank you for your help.


1610812040009.png


1610812133154.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,930
Office Version
  1. 365
Platform
  1. Windows
Neither of those codes are the code I supplied.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,258
Messages
5,641,178
Members
417,196
Latest member
Liziz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top