如何使用VBA在Excel中基于剪贴板进行过滤？ [英] How to filter based on clipboard in Excel using VBA?

问题描述

我与部门中的许多人一起工作，并始终与他们共享我的代码，因此您也将帮助我和我的同事！

I work with a bunch of people in my department and always share my code with them so you'll be helping me and my coworkers as well!

使用宏记录器我已经创建了此代码来过滤要过滤的列，但是我需要根据剪贴板上的内容（我按过 Ctrl + C ）。录制宏时，我按下了 Ctrl + C ，但是它没有记录这些按钮的按下，它只是将当时剪贴板中的内容粘贴到了宏中。

Using the macro recorder I have created this code to filter the column I want filtered, but I need to change the code to filter it based on what ever is on the clipboard (what ever cell I pressed Ctrl+C on previously). I pressed Ctrl+C while recording the macro but it didn't record those button presses, it only pasted what was in my clipboard at the time into the macro.

Sub Filter()

    ActiveSheet.Range("$A$1:$V$12955").AutoFilter Field:=2, Criteria1:= _
        "Clipboard"

End Sub

推荐答案

这是我在Excel中所做的第一个项目之一。我将解释我的代码如何工作，然后您可以根据需要进行操作。

This is one of the First Projects That I did in Excel. I will explain How my code is working, then you can manipulate this according to your need.

如何使用YouTube链接

将代码粘贴到之后个人宏，在快速访问工具栏上为此宏创建一个快捷方式。

After Pasting the code in your Personal Macro, Create a Shortcut on the Quick Access ToolBar for this Macro.

然后，您需要做的就是，应用自动过滤，在要过滤的范围上，复制包含要过滤的值的单元格或单元格范围，然后选择要在其上应用此过滤器的列的标题。按下创建的快捷键。

Then All you need to do is, Apply Autofilter on the Range you want to Filter, Copy the Cell or the Range of Cells that contains the value you want to filter, and select the header of the column on which you want to apply this Filter. Press the Created Shortcut.

它也可以在单个单元格上使用，因此，您无需修改​​它。只需按照上面的说明进行操作即可。

此处是代码：

Sub filtrr()
'
    Dim i As Integer
    Dim Test As String
    Dim clipboard As MSForms.DataObject
    Set clipboard = New MSForms.DataObject

    clipboard.GetFromClipboard
    Test = clipboard.GetText

    Test = Replace(Test, Chr(13), "-")
    Test = Trim(WorksheetFunction.Clean(Test))

    Dim ab() As String

    ab = Split(Test, "-")

    ReDim Preserve ab(UBound(ab) - 1)

    ActiveSheet.UsedRange.AutoFilter Field:=Selection.Column, Criteria1:=ab, Operator:= _
        xlFilterValues


End Sub

在您的情况下，只有一个包含值的单元格，但是此代码如果要在成千上万个值的表中过滤掉50个值，则可以节省生命。

In your case it's only One cell that contains value, but this code is a life saver if you want to filter like 50 values in a table of thousands of values.

这篇关于如何使用VBA在Excel中基于剪贴板进行过滤？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！