在VBA中获取行的总复制集并将其存储在变量中 [英] Getting a total copied set of rows in VBA and storing it in a variable
问题描述
我有一个非常简单的语法问题:
I have a fairly simple syntax question:
我正在尝试将n行从一个Excel文件复制并粘贴到另一行.另外,我想将所有复制的行存储到一个变量中.有人可以帮我做到这一点吗?
I'm trying to copy and paste n rows from one excel file to another. In addition, I'd like to store the total copied rows into a variable. Can someone help me accomplish this?
例如:
1)
Activate CSV file
Apply Filter to Column B (Page Title) & uncheck "blanks" ("<>") filter**
Windows("Test_Origin.xlsm").Activate
ActiveSheet.Range("$A$1:$J$206").AutoFilter Field:=2, Criteria1:="<>"
2)
Copy Filtered Lines with data (Excluding Row 1)
Range("B2:F189").Select
Selection.Copy
copiedRowTotal = total *FILTERED* rows copied over from original sheet, then Test Number iterates that many times
copiedRowTotal = Selection.Rows.Count
MsgBox copiedRowTotal
谢谢
推荐答案
一种间接的方法是
Range("B2:F189").Copy
Range("M2").PasteSpecial xlPasteValues
copiedRowTotal = Selection.Rows.Count
Selection.Clear
代码复制范围&在单独的位置执行粘贴特殊操作.
这样,仅将过滤后的行复制到M2&中.完成 PasteSpecial
操作后,将突出显示该区域(粘贴过滤的行).
The code copies the range & does a paste special operation on a separate location.
By doing this, only filtered rows are copied to M2 & the area (where the filtered rows are pasted) is highlighted when PasteSpecial
operation is done.
执行 Selection.Rows.Count
给出一个,即粘贴的已筛选的行数.
在找出过滤出的行数之后,将清除选择内容.
Doing a Selection.Rows.Count
gives one, the number of filtered rows that were pasted.
After figuring out the number of filtered rows, the selection is cleared up.
这篇关于在VBA中获取行的总复制集并将其存储在变量中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!