'Module
'Returns variant array Base 1.
Function StringTo2dArray(s As String, Optional rowDelim = vbCrLf, Optional colDelim = vbTab)
Dim i As Long, j As Integer, a, aa, b, c As Integer, r As Long
a = Split(s, rowDelim)
'Trim empty last element if needed. e.g. Range copied to clipboard as s.
If Len(a(UBound(a))) - Len(Replace(a(UBound(a)), vbTab, "")) = 0 Then _
ReDim Preserve a(0 To UBound(a) - 1)
r = UBound(a) + 1
c = UBound(Split(a(0), colDelim)) + 1
ReDim b(1 To r, 1 To c)
For i = 1 To r
aa = Split(a(i - 1), colDelim)
For j = 1 To c
b(i, j) = aa(j - 1)
Next j
Next i
StringTo2dArray = b
End Function
Function GetClipboard()
'Early bound MSForms.DataObject
'Tools > References > Microsoft Forms 2.0 Object Library
'Dim MyData As DataObject
'Late bound MSForms.DataObject
Dim MyData As Object
Set MyData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
On Error Resume Next
Set MyData = New DataObject
MyData.GetFromClipboard
GetClipboard = MyData.GetText
End Function
'uf2
Private Sub UserForm_Initialize()
With Worksheets("AOIData")
ListBox1.ColumnCount = .UsedRange.Columns.Count
ListBox1.List = .UsedRange.Value
ComboBox1.List = .Range("B2", .Cells(.Rows.Count, "B").End(xlUp)).Value
End With
ComboBox1.AddItem ""
End Sub
Private Sub ComboBox1_Change()
Dim a
With Worksheets("AOIData")
If ComboBox1.Value = "" Then
ListBox1.List = .UsedRange.Value
Exit Sub
End If
.UsedRange.AutoFilter 2, Format(CDate(ComboBox1.Value), "m/d/yyyy h:mm")
.UsedRange.SpecialCells(xlCellTypeVisible).Copy
a = StringTo2dArray(GetClipboard)
Application.CutCopyMode = False
ListBox1.List = a 'WorksheetFunction.Transpose(a)
End With
End Sub
Private Sub CommandButton1_Click()
Application.CutCopyMode = False
With Worksheets("AOIData")
If .AutoFilterMode Then .UsedRange.AutoFilter
End With
Unload Me
End Sub
