• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Autofilter with Specialcells

HI There.
I have copied my code underneath, and i wonder if you could please assist
The program is about clients buying a 'Funeral policy', which includes a 'grave' in the Memorial Park.
The Sheets "Orders" ( shOrders), holds each order number , whereas the sheet "Beneficiaries_Burials" ( sh) holds how many beneficiaries are covered under each policy in this particular order ( policy).
The final objective of the code is to print in the sheet "R_Purchase_Summary", all the details of the Order ( including all beneficiaries).
The problem that I have is, that sometimes the order itself has NOT yet been allocated beneficiaries names, and hence there are NO beneficiaries to be extracted when querying the "sh" sheet ( see Autofilter method , within the first With statement). When there are NO beneficiaries it prints the whole filtered list instead of "nothing" (blank) , as it should be.
I've tried the 'On error" ( but is NOT really an error)..... I also tried Specialcells , but i am not quite sure to make it work.
Hope i was clear with my explanation , otherwise please let me know. Many Thanks

as You have already noted few times
>>> use code - tags <<<
Code:
Private Sub CmdbuttonPurchaseSummary_Click()

  Application.ScreenUpdating = False
  Dim sh As Worksheet
  Dim dsh As Worksheet
  Dim shOrders As Worksheet
  Dim ordernumber As Integer
  Dim fnd As Range

    Set sh = ThisWorkbook.Sheets("Beneficiaries_Burials")
    Set dsh = ThisWorkbook.Sheets("R_Purchase_Summary")
    Set shOrders = ThisWorkbook.Sheets("Orders")
  
    '*** The code starts by requesting an order number (ordernumber), to be extracted from the shOrders sheet and also checks if the Order number exists ****
    ordernumber = Application.InputBox(Prompt:="Enter Order Number", Type:=1)
    Set fnd = shOrders.Range("B:B").Find(ordernumber, LookIn:=xlValues, Lookat:=xlWhole)
    If fnd Is Nothing Then
        MsgBox ("Order Number not found. Please try again.")
        Exit Sub
    End If
  
    dsh.Range("C21:U44").ClearContents
  
    '**** Here the code copies all the records found in sh worksheet that need to be copied to be pasted in the dsh sheet within the next With statement ****
    With sh
        .AutoFilterMode = False
        .Range("Beneficiaries_Burials").AutoFilter Field:=2, Criteria1:=ordernumber
        .Range("Beneficiaries_Burials").Copy
        .AutoFilterMode = False
    End With
  
    '*** Here the code pastes in the dsh sheet, the data filtered from the sh sheet above *****
    With dsh
        .Range("C11").Value = ordernumber
        .Range("C21").PasteSpecial xlPasteValues
    End With
      
    If sh.Range("Beneficiaries_Burials").AutoFilter Then
    sh.Range("Beneficiaries_Burials").AutoFilter
    End If
    Application.ScreenUpdating = True
End Sub
 
Last edited by a moderator:
martinargimon
Instead of ... fnd ... count number of filtered rows.
Code:
Frows = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1
Without a sample file, You would modify it Yourself.
 
Hi vletm
Thanks very much for your input.
Perhaps i did not explain myself properly but the main issue is really not on checking if the Order number exists or not , referring o the 'find (set fnd) variable.
The problem arises within the 'With sh' statement, that is where the code goes to fetch data in the .range ("Beneficiaries_Burials") , and sometimes it finds nothing to copy ( as there are No entries for the specific ordernumber, and hence when it pastes the data in the 'dsh' table it brings a lot of 'rubbish' ( i.e. a full list of the filtered table, i presume) , and not just 'blanks' ( since there is nothing found)
I hope it makes sense , sorry to trouble you. Kindly let me know if i make sense?
If you prefer i can send you the excel workbook. ( is 2244KB ), so i let me know how to attach it.
Thanks again . Regards. Martin
 
martinargimon
What is Your problem?
Did You miss to know from Your filter, do it shows any rows or what?
... sense ... no.
I tried to ask a sample file, which has same kind of challenge.
 
Hi,​
as this is just a Logic concern … Obviously you can check if something 'matches' with the filter via SUBTOTAL worksheet function​
- even under VBA - after filtering or just with MATCH worksheet function before filtering anything …​
As a reminder better than using a filter is an advanced filter which filters & copies at once only the matching rows.​
 
martinargimon
Did You read my questions?
Anyway, I didn't get needed answers.
I answered Your 'sense'-question.
Did You skip my the last sentence? ... a sample file
... ~22MB ... would be too huge file for sample.
 
Thanks vletm
I was able to fix the problem using the Advanced filter rather than the Autofilter, in a much easier way
Thanks for your tip
Kind Regards
Martin
 
Back
Top