Dynamic Array

[CPAExcelGuy]

Hello,

I'm interested in creating a dynamic array. Essentially, I have a list of locations, and I would like that list of locations to populate into a subsequent list whenever someone places an "x" next to it. Like in the table below. Can someone please help?
[TABLE="width: 500"]
<tbody>[TR]
[TD]Locations[/TD]
[TD]Place an "X"[/TD]
[TD][/TD]
[TD][/TD]
[TD]Locations to Populate[/TD]
[/TR]
[TR]
[TD]Chicago[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Chicago[/TD]
[/TR]
[TR]
[TD]Los Angeles[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]New York[/TD]
[/TR]
[TR]
[TD]Dallas[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New York[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nashville[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[manu197a]

I think this video could help you: https://www.youtube.com/watch?v=vkPoViUhkxU

 

[bhos123]

Sub Macro2()
Dim lngLastRow As String
Dim lastRow As Long
Dim lastcolumn As Long
Dim j As Long
j = 2
Columns("D:E").Select
Selection.ClearContents
lastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
For i = 2 To lastRow
If Cells(i, 2).Value = "X" Then
Cells(j, 5).Value = Cells(i, 1).Value
Cells(j, 4).Value = j - 1
j = j + 1
End If
Next
End Sub

you might want to run this macro eachtime, as private macos are not possible in this case.