Autofilter Runtime Error

[Emeng]

Hi guys

My code throws 1004 runtime error "No cells found" when copying autofilter results. The line immediately before the error as follows,

If Range("J1:K" & lastrow).SpecialCells(xlCellTypeVisible).Count > 1 Then

There is no data in this instance, only a header row & I'm puzzled why the preceding line is ignored.
Any help is much appreciated.
Click on the Create Plans button in the attached file.
Thanks & regards
Mark

 

[NARAYANK991]

Hi ,

The earlier line of code is :

Range("J1:K" & lastrow).SpecialCells(xlCellTypeVisible).Count

and when you check this out in the Immediate Window , the count is 112.

The line of code which generates an error is :

.Range("A2:A" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Copy

with the error being No Cells Found.

The problem is the insignificant but highly significant period , which has been highlighted in red.

In the absence of the dot operator , the range refers to the active sheet , which is the sheet named Data ; with the dot operator , the range refers to the sheet named Mechanical.

You need to work out which one is correct ; if the dot is required in the earlier line of code , put it in , and if there are no records , there will not be any error generated in the next line of code.

Narayan

 

[NARAYANK991]

Hi ,

This same problem exists in several other places , and all of them will need to be corrected.

Narayan

 

[Emeng]

Hi Narayan
I see that by leaving out the period in the If statement I am referring to the same range on a different sheet.
I changed to using the longhand version as follows

Sub CopyTasks2()

    Dim lastrow As Long
    Dim lr As Long
    lastrow = Sheets("Mechanical").Range("A" & Rows.Count).End(xlUp).Row
         lr = Sheets("Electrical").Range("A" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = 0
   
    'With Sheets("Mechanical")
            Sheets("Mechanical").Activate
       
            Sheets("Mechanical").Range("J1:K" & lastrow).AutoFilter Field:=1, Criteria1:="ELEC"
           
            If Sheets("Mechanical").Range("J1:K" & lastrow).SpecialCells(xlCellTypeVisible).Count > 1 Then
            Sheets("Mechanical").Range("A2:A" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Copy
            Sheets("Electrical").Range("A" & lr + 1).PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, False, False
            Sheets("Electrical").Range("A" & lr + 1).PasteSpecial xlPasteFormats
            End If
            Sheets("Mechanical").AutoFilterMode = 0
        With Application
            .CutCopyMode = 0
            .GoTo Sheets("Electrical").Range("A2")
        End With
    'End With

however I get the same runtime error on the same line, trying to copy when no data exists.

Regards Mark

 

[NARAYANK991]

Hi ,

The line of code :

Sheets("Mechanical").Range("J1:K" & lastrow).SpecialCells(xlCellTypeVisible).Count

is returning 2 , because this is the header row , which is always visible , and since you are using two columns , at least the two header cells J1 and K1 will always be visible.

The mistake is in including the header row , or in using 1 as the threshold.

Modifying the above line of code to :

If Sheets("Mechanical").Range("J1:K" & lastrow).SpecialCells(xlCellTypeVisible).Count > 2 Then

will ensure that unless there is at least one row of data visible , the next line of code will never get executed.

You can use the attached file , which retains the With statements , and has the modified code.

Narayan

 

[Emeng]

Hi Narayan

Thanks for your help, it seems so obvious now that you have pointed it out.

Regards

Mark