How to populate all the rows in one column based on one cell value

[DocAElstein]

I did it 'my way':.....

Hi snb

In my unconventional self learnt Excel VBA I seem to have missed out on the AutoFilter stuff, But I think I see what is going on here

'  snb did it his way :) http://www.excelfox.com/forum/showthread.php/2141-How-to-populate-all-the-rows-in-one-column-based-on-one-cell-value?p=10040#post10040
Private Sub Worksheet_Change(ByVal Target As Range) ' Code 1:          Already Dim'ed...*** , like the worksheet of a class worksheet already there, do this is just an instance or like. Checks through to see if these things are there, Kicks in then also when Worksheet is changed      "watcher is already there" http://www.excelforum.com/showthread.php?t=1162925&page=2&p=4522463#post4522463
    If Target.Address = "$E$1" And (Target.Value = "Issue" Or Target.Value = "Signed off") Then ' Target is the reseved name for the Range where the change takes place ...   when that is (just) E Then ...
      With Cells(1).CurrentRegion ' With the Range object starting at A1 that is enclosed by a free row and column..
       .AutoFilter 2, Target.Value ' Autofilter for whole range based on second column being the Target value
       Application.EnableEvents = False ' ### stop code kicking in again
       .Resize(, 1).Copy Destination:=Cells(1, 5) ' Copying of a filtered Range seems to just copy what is seen. Only copying a range of the existing rows, but just the first column. "Shortcut" copy destination bypassing clipboard maybe..        http://www.eileenslounge.com/viewtopic.php?f=27&t=25002#p195791    This line sets the routine off again but for the bigger range it does nothing but including the Target.Value causes error as it retuens then an Array so  ' ###
       Application.EnableEvents = True
       .AutoFilter ' This turns the Filtering off, so we "see all" again
      End With
   End If
End Sub
    '   I cHaNgEd the cHaNgE above - it got cHaNgEd here automatically... that is because...*** Already Dim'ed ... but comment this out or it error as if you Dimed a variable twice !!°
'    Private Sub WoRkShEeT_cHaNgE(ByVal Target As Range) ' Code 2: It appears that the autofilter when applied "sees all" even if we do not - so it will always work
'        If Target.Address = "$E$1" And (Target.Value = "Issue" Or Target.Value = "Signed off") Then Cells(1).CurrentRegion.AutoFilter 2, Target.Value '
'    End Sub

_...

_....and then
' I did it in a strings Functional way

Private Sub Worksheet_Change(ByVal Target As Range)
    If IsArray(Target.Value) Then                                             ' Need to check that selection is not a Range object of more than one cell, if it is , for example then .Value prooperty willreturn an Array
                                                            ' Then if we have an Array we come here,, do nothing, so go to  End If '_-1
    Else                                                          ' for a single cell value change:-
        If Target.Address = "$E$1" And (Target.Value = "Issue" Or Target.Value = "Signed off") Then
        Dim RngAllcells As Range: Set RngAllcells = Cells ' Cells property returnng a Range object of all cells in object to which it is applied. We are in the Worksheet class module of a Worksheet so Cells unqualified call goes there
        Dim arrIn() As Variant: Let arrIn() = RngAllcells.Item(1).CurrentRegion.Value                                                                   ' Range Item Property applied to top left of Range Object of all worksheet cells ' http://www.excelfox.com/forum/showthread.php/2138-Understanding-VBA-Range-Object-Properties-and-referring-to-ranges-and-spreadsheet-cells?p=10012#post10012
        Dim ConcatedWonk() As String                                                           ' Array for string values based on concatenation of first two columns. We do not need a dynamic Array but must dimension it as in next line, because...
         ReDim ConcatedWonk(1 To UBound(arrIn(), 1))                            '                                 ...Must use Re Dim method as Dim declaration only takes values
        Dim Cnt As Long                                                           ' Loop bound variable count
            For Cnt = 1 To UBound(arrIn(), 1) Step 1
             Let ConcatedWonk(Cnt) = arrIn(Cnt, 1) & "|" & arrIn(Cnt, 2)                                                                         'String built from both column values per row
            Next Cnt
        Dim arrFilt() As String: Let arrFilt() = VBA.Strings.Filter(ConcatedWonk(), Target.Value, True)                                     'Filters True in all member elements of ConcatedWonk() that have the Target value as part of that concatenated string
        Dim arrFiltT() As String                                                                           ' We will both transpose the filtered member element  and chop out the bit in it after and inclusding the "|" which leaves us with our column A value only. Once again the dynamic array is not needed , but we must do it this way to use other than numbers in the sizing.
         ReDim arrFiltT(1 To (UBound(arrFilt()) + 1), 1 To 1)                                                                         ' An Array 2 Dimension 1 column for transposed column A values
            For Cnt = 1 To (UBound(arrFilt()) + 1) Step 1
             Let arrFiltT(Cnt, 1) = VBA.Strings.Left(arrFilt(Cnt - 1), InStr(1, arrFilt(Cnt - 1), "|") - 1)                                                                        'Each "vertical" member element is given the "horizontal" form the filtered Array but only the string part from left of concatenated string to the point just before the "|"
            Next Cnt
          Let Range("E1").Resize(UBound(arrFiltT(), 1), UBound(arrFiltT(), 2)).Value = arrFiltT()                                                                                       '.value property is applied to a Range object of size of that of the top left of where we want the output resized to suit the dimensions of the Array. The presented field of the memory locations for the value may be assigned directly an Array of values
        Else                                                             ' Targeted somewhere else , so do nothing. Redundant code
        End If
    End If                                                                                          '_-1 had a multi cells selection change
End Sub