在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屋！