1

I was running some scenarios with my Excel 2016 VBA code and came to a problem which I can't seem to solve. So, the code below does this:

  1. remove any existing filters on worksheet "Data"
  2. apply new filters for the table on sheet "Data" so that column "Location" would be filtered based on cell "G2" value (i.e. "South") + column "Quantity" would contain only non-blanks
  3. select now filtered data range + Copy selection (entire potential range highlighted on the screenshot - i.e E5:F12)
  4. Activate worksheet "Transit" and go to first empty cell in highlighted range (A4:A10)
  5. Perform Paste-values + de-activate filtering on the previous sheet

It works, however with one crucial exception (should it matter, I'm launching the code from worksheet Transit)

In a scenario when there's nothing to copy, it copies everything (i.e. the entire "Data_range"). Based on screenshot below when location "South" is filtered, there are no rows with non-blank "Quantity". In a scenario like that, instead of copy-pasting nothing, it does the opposite and copy-pastes the entire range with no filtering.

I can't manually even perform such a procedure - is there a way to halt the code, in case no filtering matches are found? Any other way to avoid such an excessive pasting? 

Sub Copy()

    On Error Resume Next
    Worksheets("Data").ShowAllData

    With Sheets("Data")
    .Range("Items").AutoFilter Field:=2, Criteria1:=.Range("G2").Value
    .Range("Items").AutoFilter Field:=5, Criteria1:="<>"    

    Application.Goto Reference:="Data_range"
    Selection.Copy

    Worksheets("Transit").Activate

    For Each cell In Range("First_empty_row").Cells
    If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell

    Selection.PasteSpecial Paste:=xlPasteValues

    .Range("Items").AutoFilter Field:=2
    .Range("Items").AutoFilter Field:=5

End With
End Sub

enter image description here

sql scholar
  • 199
  • 1
  • 2
  • 13
  • 2
    You could check out this answer on counting the number of visible rows in a filtered list object: https://stackoverflow.com/a/34689072/9259306 Change it to a function to return the count (might need to edit it to ensure it returns `0` when empty) then just compare the count to `>0`. – Mistella May 29 '18 at 19:48
  • Thanks - I must admit the answer there ended up being too complicated for me to implement, however it induced me to look for a solution in a different manner and found a thing working for me here (posted by user ashu1990): https://www.ozgrid.com/forum/forum/help-forums/excel-general/136803-stop-copy-paste-if-the-filtered-range-has-no-data – sql scholar May 29 '18 at 22:58

0 Answers0