excel vba-在自动过滤后选择除标题以外的所有已过滤行 [英] excel vba - Select all filtered rows except header after autofilter
问题描述
我正在尝试编写宏以执行以下操作:
I'm trying to write a macro to do the following:
- 从Sheet1观看A列中输入的数据;
- 当我在A列的单元格中写入内容时,使用该值过滤Sheet2;
- 过滤完成后,将列标题以外的所有内容从第二张工作表复制到第一张工作表中,即使有多个值也是如此.
我试图写这篇文章:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("A:A")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
copy_filter Target
End If
End Sub
Sub copy_filter(Changed)
Set sh = Worksheets("Sheet2")
sh.Select
sh.Range("$A$1:$L$5943") _
.AutoFilter Field:=3, _
Criteria1:="=" & Changed.Value, _
VisibleDropDown:=False
Set rang = sh.Range("$A$1:$L$5943") _
.SpecialCells(xlCellTypeVisible)
rang.Offset(0, 0).Select
Selection.Copy
Worksheets("Sheet1").Select
Worksheets("Sheet1").Range(Changed.Address).Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues
sh.Range("$A$1:$L$5943").AutoFilter
Application.CutCopyMode = False
End Sub
但是,当我复制选择内容时,标题行也会被复制,但是使用.Offset(1,0)会剪切标题和另外1行,并且无法解决过滤器未返回任何结果的情况.
However when I copy the selection the header row gets copied as well, but using .Offset(1, 0) cuts the header and 1 additional row and doesn't account for cases when the filter returns no results.
如何选择除标题之外的所有已过滤行?
How can I select every filtered rows except for the header?
推荐答案
使用 sh.UsedRange
将为您提供动态范围.但是, sh.Range("$ A $ 1:$ L $ 5943")
不会缩小并增长到与您的数据集匹配.
我们可以这样修剪标题行:
Use sh.UsedRange
will give you a dynamic range. Where as, sh.Range("$A$1:$L$5943")
will not shrink and grow to match your dataset.
We can trim the header row off like this:
Set rang = sh.UsedRange.Offset(1, 0)
Set rang = rang.Resize(rang.Rows.Count - 1)
但是 SpecialCells(xlCellTypeVisible)
将抛出找不到单元格.
错误,如果没有数据要返回.所以我们必须捕获这样的错误:
But SpecialCells(xlCellTypeVisible)
will throw a No cells were found.
error if there is no data to return. So we'll have to trap the error like this:
On Error Resume Next
Set rang = rang.SpecialCells(xlCellTypeVisible)
If Err.Number = 0 Then
End If
On Error GoTo 0
Sub copy_filter(Changed)
Dim rang As Range
Set sh = Worksheets("Sheet2")
sh.UsedRange.AutoFilter Field:=3, _
Criteria1:="=" & Changed.Value, _
VisibleDropDown:=False
Set rang = sh.UsedRange.Offset(1, 0)
Set rang = rang.Resize(rang.Rows.Count - 1)
On Error Resume Next
Set rang = rang.SpecialCells(xlCellTypeVisible)
If Err.Number = 0 Then
rang.Copy
Worksheets("Sheet1").Range(Changed.Address).Offset(0, 1).PasteSpecial Paste:=xlPasteValues
End If
On Error GoTo 0
sh.Cells.AutoFilter
Application.CutCopyMode = False
End Sub
这篇关于excel vba-在自动过滤后选择除标题以外的所有已过滤行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!