Replacing an On Error with an If, regarding copying cells that may not exist after a filter

[RockandGrohl]

Hi everyone.

I'm using VBA Autofilter to cut down a large database of products and copying whats left over to a temp sheet, it's defined like this:

LastrowAD = Cells(Rows.Count, "A").End(xlUp).Row
Dim TourCopyRng As Range
Dim DateCopyRng As Range
Dim NameCopyRng As Range
Dim CostCopyRng As Range


Set TourCopyRng = Range("A2:A" & LastrowAD)
Set DateCopyRng = Range("E2:E" & LastrowAD)
Set NameCopyRng = Range("C2:C" & LastrowAD)
Set CostCopyRng = Range("G2:G" & LastrowAD)


### USING FILTERS TO REMOVE CRITERIA ###

On Error GoTo NoTours
TourCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("A12")
NameCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("B12")
DateCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("C12")
CostCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("D12")
ad.Close False
On Error GoTo 0

I think that last bit at the bottom was causing errors further down the line, so as a test I have replaced it with:

If TourCopyRng.SpecialCells(xlCellTypeVisible) Is Nothing Thenad.Close False
GoTo NoTours
Else
TourCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("A12")
NameCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("B12")
DateCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("C12")
CostCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("D12")
ad.Close False
End If

However this doesn't seem to work, any ideas what I'm doing wrong? Thanks.

 

[Norie]

Perhaps.

On Error Resume Next
Set TourCopyRng = TourCopyRng.SpecialCells(xlCellTypeVisible)
On Error Goto 0

If Not TourCopyRng Is Nothing Then
    TourCopyRng.Copy temp.Range("A12")
    NameCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("B12")
    DateCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("C12")
    CostCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("D12")
Else
    MsgBox "No data to copy!"
End If

 

[RockandGrohl]

I'll give this a shot, but I am trying to completely step away from On Errors - I have so many in this set of VBA and it's been giving me unspecified error crashes.

EDIT: Solved it like this:

If Not Range("A1:A" & LastrowAD).SpecialCells(xlCellTypeVisible).Count > 1 Then
ad.Close False
GoTo NoTours
Else
TourCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("A12")
NameCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("B12")
DateCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("C12")
CostCopyRng.SpecialCells(xlCellTypeVisible).Copy temp.Range("D12")
ad.Close False
End If

From here

https://www.mrexcel.com/forum/excel-questions/680332-vba-if-autofilter-contains-no-records.html

 

[Rick Rothstein]

The On Error code lines in Norie's code do not persist and should not affect any other code. With that said, if you can guarantee that at least one row will be visible when you run the macro, you can remove the two On Error code lines as they are there only to protect against no row being visible.

 

[RockandGrohl]

I was wasting my time looking on stackoverflow when the answer was right here on mrexcel (as usual!) look at my edit above ta.

 

[Rick Rothstein]

I was wasting my time looking on stackoverflow when the answer was right here on mrexcel (as usual!) look at my edit above ta.

If your filter is hiding every row in the range being examined, the you code will stop with a "No Cells Found" error. See what I wrote in Message #4 .

 

[RockandGrohl]

Sure, but I am just trying to get rid of every instance of On Error, where I can use an If statement to check for a criteria before getting an error.

There's something funny going on with this macro I'm running. Google returns absolutely no results, I am getting an "Automation Error" pop-up, then Excel closes. It happens completely randomly and I get no error code or anything verbose at all. Since changing my VBE settings to "Break on all errors" instead of unhandled, I finally stopped crashing so regularly. Since changing the On Error to an If statement, I haven't crashed with "Automation Error" at all.

I edited my post with, I believe, an elegant solution that does not require any "On Errors"

 

[Norie]

If you want to find if there's any data to copy why not use Application.worksheetfunctionthatcountsthings with the criteria you are using in the filter?