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

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
351
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
Joined
Apr 28, 2004
Messages
75,288
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
Joined
Aug 1, 2018
Messages
351
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
Joined
Apr 18, 2011
Messages
35,425
Office Version
2010
Platform
Windows
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
Joined
Aug 1, 2018
Messages
351
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
Joined
Apr 18, 2011
Messages
35,425
Office Version
2010
Platform
Windows
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
Joined
Aug 1, 2018
Messages
351
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
Joined
Apr 28, 2004
Messages
75,288
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:

Forum statistics

Threads
1,078,309
Messages
5,339,410
Members
399,302
Latest member
Swiftymj

Some videos you may like

This Week's Hot Topics

Top