Excel VBA遍历可见的筛选行 [英] Excel VBA loop through visible filtered rows

问题描述

我有一个带有自动过滤器的excel表.

I have a excel table with a autofilter.

在过滤后的表格中，我只过滤了几行.

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.

我的代码草稿是:

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

推荐答案

您的代码包含几个错误，您提供的其他信息很少，可以帮助我们，但是请您尝试一下.

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

这篇关于Excel VBA遍历可见的筛选行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！