从复制的过滤单元格中获取数据 [英] Get the data from copied filtered cells

问题描述

如果我从过滤列中复制一些单元格（仅包含数字），那么我如何进一步从VBA中引用这些数据？

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

如果我尝试这样：

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

DataObj.GetFromClipboard

Debug.Print DataObj.GetText(1)

我收到此错误：

DataObject:GetText Invalid FORMATETC structure

有没有其他方法可以从复制的过滤单元格中访问数据？

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

推荐答案

对于我来说，

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

这篇关于从复制的过滤单元格中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！