Excel VBA 循环遍历可见的过滤行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33249555/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA loop through visible filtered rows
提问by Joao Santos
I have a excel table with a autofilter.
我有一个带有自动过滤器的 excel 表。
In the filtered table i only have few rows filtered.
在过滤表中，我只过滤了几行。
My objective is icterate all visible rows to colect data to copy to anothe sheet.
我的目标是遍历所有可见行以收集数据以复制到另一个工作表。
I want a way to collect a variable with the the fisrt visible row number.
我想要一种方法来收集具有第一个可见行号的变量。
my draft code is:
我的草稿代码是：
Dim cnp As String
Dim nome As String
Dim filter_rng As Range
Dim rw As Range
Dim last_row As Long 'last visible data row
Dim dest_row As Long 'row to paste the colected data
Set filter_rng = Range("A5:Y" & last_row).Rows.SpecialCells(xlCellTypeVisible)
'collect data
For Each rw In filter_rng.SpecialCells(xlCellTypeVisible)
workshett(1).Activate
cnp = Range("a" & rw).Value
nome = Range("b" & rw).Value
'copy data to another worksheet first data line is cell A2
Worksheet(2).Activate
Range("A" & dest_row + 1).Value = cnp
Range("b" & dest_row + 1).Value = nome
Next rw
回答by mtholen
Your code contains several errors and you provide little additional information to allow us to help you, but to put in an attempt.
您的代码包含多个错误，您提供的附加信息很少，无法让我们为您提供帮助，但只是尝试。
Please see below code and compare to yours, the below code is closest to what you are trying to do and is tested and working.
请参阅下面的代码并与您的代码进行比较，下面的代码最接近您尝试执行的操作并且经过测试和工作。
Dim cnp As String
Dim nome As String
Dim filter_rng As Range
Dim rw As Range
Dim last_row As Long 'last visible data row
Dim dest_row As Long 'row to paste the colected data
last_row = 200
dest_row = 1
Set filter_rng = Sheets(1).Range("A5:Y" & last_row)
'collect data
For Each rw In filter_rng.SpecialCells(xlCellTypeVisible)
'Worksheets(1).Activate
cnp = Sheets(1).Range("A" & rw.Row).Value
nome = Sheets(1).Range("B" & rw.Row).Value
'copy data to another worksheet first data line is cell A2
'Worksheets(2).Activate
Sheets(2).Range("A" & dest_row + 1).Value = cnp
Sheets(2).Range("B" & dest_row + 1).Value = nome
Next rw
