VBA to create list based on input

[cvrband]

I would like help creating VBA that creates a unique list based on input in cell 'H1' and provides vertical output starting at cell 'E5'. (cell 'H1' will always be a 1- or 2-digit number)

Starting in cell 'A5' of the sheet named "Products" I have a list of unique product numbers. Starting in cell 'C5' I have a list of product types. Because the number of products varies from project to project, the last row of both columns 'A' & 'C' needs to be determined by column 'B' ( LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row ) due to values input by user in column 'B'. There will be no blank rows in column 'B' but values in columns 'A' & 'C' could falsely extend past the last row of column 'B' and should not be included in the result search.

Request: VBA to provide a list starting at 'E5' of products (column 'A') that match product type (column 'C') and the value that the user input in cell 'H1' without blank rows between the results?

I plan to fire this routine with a button, which I am able to set up.

Thank you in advance.

 

[alansidman]

Can you provide some sample data and show a before and after scenario of what you are looking for. Your explanation is difficult to follow and we need more specific information regarding products and product type to formulate any type of code.

 

[cvrband]

Thanks for your reply alansidman. I apologize that my description was confusing. A sample spreadsheet can be found here: https://app.box.com/s/fowon64e2f6wqygi3f7pvu9ejx8dihe6

Please let me know if this doesn't clarify my request. Thank you for your help.

 

[Peter_SSs]

Firstly, do you really need a macro? This formula in E5 and copied down as far as you might ever need should give you the list.

=IFERROR(INDEX(A$5:A$1000,AGGREGATE(15,6,(ROW(A$5:A$1000)-ROW(A$5)+1)/((C$5:C$1000=H$1)*(B$5:B$1000<>"")),ROWS(E$5:E5))),"")


If you do need a macro, then try this in a copy of your workbook.

Sub GetProducts()
  With Range("A4:C" & Range("B" & Rows.Count).End(xlUp).Row)
    .AutoFilter Field:=3, Criteria1:=Range("H1").Value
    If .SpecialCells(xlVisible).Count > 3 Then
      .Offset(1).Resize(.Rows.Count - 1).Columns(1).Copy Destination:=Range("E5")
    End If
    .AutoFilter Field:=3
  End With
End Sub

 

[cvrband]

Thank you Peter_SSs, your code works great! I was using an array formula similar to what you suggested and it was causing problems. I also wanted to cut out extra calculations in this large spreadsheet. Thanks again for your help!

 

[Peter_SSs]

Thank you Peter_SSs, your code works great! I was using an array formula similar to what you suggested and it was causing problems. I also wanted to cut out extra calculations in this large spreadsheet. Thanks again for your help!

You're welcome. Thanks for the extra explanation.