excel - Get the data from copied filtered cells

Question

If I copy some cells (simply containing numbers) from filtered column, how can I further reference this data from VBA?

If I try this:

Dim DataObj As MsForms.DataObject
Set DataObj = New MsForms.DataObject

DataObj.GetFromClipboard

Debug.Print DataObj.GetText(1)

I get this error:

DataObject:GetText Invalid FORMATETC structure

Is there any other way to access the data from copied filtered cells?

Answer 1

This works for me.

Sub Tester()

Dim DataObj As MsForms.DataObject
Dim rng As Range, c As Range, rngV As Range

    Set rng = Sheet1.Range("A2:A20") 'filtered range
    On Error Resume Next
    Set rngV = rng.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If rngV Is Nothing Then
        Debug.Print "No visible cells!"
        Exit Sub
    End If

    'using Clipboard
    rngV.Copy
    Set DataObj = New MsForms.DataObject
    DataObj.GetFromClipboard
    Debug.Print DataObj.GetText '>> vbCrLf-delimited string

    'direct read
    For Each c In rngV.Cells
        Debug.Print c.Value
    Next c


End Sub