CODEWITHSUNDEEP
vbaexcel
Verd'O
Verd'O

Reputation: 101

How to deal with no value after a filter

This is my data base: This is my data base

Now, this is my sheet "product":

enter image description here

And this si my sheet "article":

enter image description here

I have this piece of code that actualy works:

    ' Extract Products
    ReDim Preserve aProducts(2 * .Rows.Count)
    lRow = 1
    sCriteria = "Produit"
    aProducts(lRow) = Array("Id Product", "Value A", "Value B", "Value C")
    aHdr = Array("Id product", "Value A", "Value B", "Value C", _
        "Type of value A", "Type of value B", "Type of Value C")
    GoSub Get_Field_Post

    ' Extract Products - Value A
    b = aPos(5)
    .AutoFilter Field:=b, Criteria1:=sCriteria
    For Each vItm In .Offset(1, 0).Resize(-1 + .Rows.Count) _
        .SpecialCells(xlCellTypeVisible).Rows
        With vItm
            lRow = 1 + lRow
            aProducts(lRow) = Array(.Cells(aPos(1)).Value2, _
                .Cells(aPos(2)).Value2, Empty, Empty)
        End With
    Next
    .AutoFilter Field:=b

The entire code is a bit longer, that's why I added a file, feel free to download it to understand the entire code.

It works perfectly except when there is an entire (except the title) empty column like this:

enter image description here

Some help would be apreciated.

Link to download the file: https://drive.google.com/file/d/0B5DpGwPWsIfbUjlueHBjTU50Mzg/view?usp=sharing

Upvotes: 0

Views: 78

Answers (1)

user3598756
user3598756

Reputation: 29421

you could go like this (see commented line):

.AutoFilter Field:=b, Criteria1:=sCriteria
If Application.WorksheetFunction.Subtotal(103, .Resize(, 1)) > 1 Then '<--| check if any cell filtered other than header
    For Each vItm In .Offset(1, 0).Resize(-1 + .Rows.COUNT) _
        .SpecialCells(xlCellTypeVisible).Rows
        With vItm
            lRow = 1 + lRow
            aProducts(lRow) = Array(.Cells(aPos(1)).Value2, _
                .Cells(aPos(2)).Value2, Empty, Empty)
        End With
    Next
End If

Upvotes: 1

Related Questions