Issue - If Statement with "Is Nothing qualifier"

[BratFire]

Recieve a "Cells Not Found" error on the If Statement. I would like, if when filtered data finds nothing, to do nothing. Instead, when the range is empty I get an error on the If statement

Suggestions appreciated.

Thank you.

  If Range(Cells(headerRow, 1), Cells(lastRow, lastCol )).SpecialCells(xlCellTypeVisible) Is Nothing Then
    msgbox(" no data to copy") 'Do nothing
  Else
    Range(Cells(headerRow , 1), Cells(lastRow, lastCol )).SpecialCells(xlCellTypeVisible).Copy
    Windows(newBook).Activate
    Range("A" & headerRow ).Select: ActiveSheet.Paste: Range("A" & headerRow).Select
    Application.CutCopyMode = False
  End If

 

[p45cal]

try (untested):

Dim myRng as Range
'On Error Resume Next
Set myrng = Range(Cells(headerRow, 1), Cells(lastRow, lastCol)).SpecialCells(xlCellTypeVisible)
'On Error Goto 0
If myrng Is Nothing Then
  MsgBox (" no data to copy")  'Do nothing
Else
  myrng.Copy
  Windows(newBook).Activate
  Range("A" & headerRow).Select: ActiveSheet.Paste: Range("A" & headerRow).Select
  Application.CutCopyMode = False
End If

You may need to enable both commented-out On Error lines

 

[VoG]

Maybe like this

On Error Resume Next
  If Range(Cells(headerRow, 1), Cells(lastRow, lastCol)).SpecialCells(xlCellTypeVisible) Is Nothing Then
    If Err <> 0 Then MsgBox (" no data to copy") 'Do nothing
  Else
    Range(Cells(headerRow, 1), Cells(lastRow, lastCol)).SpecialCells(xlCellTypeVisible).Copy
    Windows(newBook).Activate
    Range("A" & headerRow).Select: ActiveSheet.Paste: Range("A" & headerRow).Select
    Application.CutCopyMode = False
  End If
On Error GoTo 0

 

[BratFire]

Actually, I had.

Set rng = Range(Cells(headerRow + 1, 1), Cells(lastRow, lastCol + 4)).SpecialCells(xlCellTypeVisible, xlNumber)

tried with both xlNumber and without. Currently that line errors as well and I have it commented out.

 

[p45cal]

Actually, I had.

Set rng = Range(Cells(headerRow + 1, 1), Cells(lastRow, lastCol + 4)).SpecialCells(xlCellTypeVisible, xlNumber)

tried with both xlNumber and without. Currently that line errors as well and I have it commented out.

It looks looks like you'll need the On Error statements in then.

 

[BratFire]

Maybe like this

On Error Resume Next

...My code as presented above ...

On Error GoTo 0

Hmm. Kind of crapped out on me when the empty range resulted from the filter. Am I missing something?

I am sometimes leary of On Error commands in the middle of my code afraid that an intended error might not be flagged, or dealt with properly.

 

[p45cal]

Copy/paste the whole code you have now.

 

[BratFire]

Copy/paste the whole code you have now.

To quote myself "Am I missing Something?" Yes, I was. if nothing happened as a result of the IF Statment my active workbook never changed and the code would save and close the workbook I was copying from rather than the workbook I was copying to. I fixed my code, and along with with VoG's suggested code from above, and I am in business.

Thank you all for the assistance.