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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
788
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
I was wasting my time looking on stackoverflow when the answer was right here on mrexcel (as usual!) look at my edit above :) ta.
 
Upvote 0
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 .
 
Upvote 0
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"
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top