build a dynamic range from rows were a column has the same value

[davidmyers]

Hi, I have a datasheet where the value in column A is repeated several times, columns B-G are different uses and specifications for the item in column A, I want to populate a userform with all the rows (A-G) where the value in column A is the same. Column A is sorted so all the duplicate values appear together. I would like to activate the userform by clicking on column A, probably easiest if the user clicks the first occurence in column A - problem is I don't trust users - so if when I generate the range I can go from 1st to last occurence even if they don't click the 1st occurence that would be a bonus - but my main question is how to generate the dynamic range for duplicate values in column A.
Any code snippets or links to examples much appreciated.

Thanks
David

 

[Fluff]

My personal preference would be to have a combobox on the userform & the user then selects a value from the combobox.
This can then be used to filter the data & populate the listbox, something like

Option Explicit
Dim Dic As Object
Dim Rng As Range

Private Sub ComboBox1_Change()

   Range("A1:G1").AutoFilter 1, ComboBox1.Value
   ListBox1.RowSource = Rng.Offset(1).SpecialCells(xlVisible).Address
   
End Sub

Private Sub UserForm_Initialize()
   
   Dim Cl As Range
   
   Set Rng = Range("A1:G" & Range("A" & Rows.Count).End(xlUp).Row)
   Set Dic = CreateObject("scripting.dictionary")
   
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
   End With
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Not Dic.exists(Cl.Value) Then Dic.Add Cl.Value, Nothing
   Next Cl
   ComboBox1.list = Application.Transpose(Dic.keys)

End Sub

 

[davidmyers]

Hi Fluff, Thanks for the quick reply, unfortunately I'm a bit out of my depth here, but could you explain why you recommend a combo box instead of activating the userform directly from clicking on column A in the worksheet and using offset from the activecell to find the range?

Thanks
David

 

[Fluff]

but could you explain why you recommend a combo box instead of activating the userform directly from clicking on column A

I just don't trust the end user to do the right thing! And I feel that using a Combobox to select the value is probably the safest way of doing it.
But remember, no matter how idiot proof you try & make something, "The Law According to Sod" guarantees that someone will find a way of breaking it.

using offset from the activecell to find the range?

If by this you are referring to this line

ListBox1.RowSource = Rng.Offset(1).SpecialCells(xlVisible).Address

The offset is so that the header row does not get loaded into the list box