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

RockandGrohl

Active Member
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:

Code:
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:

Code:
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

Well-known Member
Perhaps.
Code:
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

Active Member
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:

Code:
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
 
Last edited:

Rick Rothstein

MrExcel MVP
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

Active Member
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

MrExcel MVP
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

Active Member
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

Well-known Member
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?
 
Last edited:

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top