CODEWITHSUNDEEP
vbaloopsrowsvisible
Joao Santos
Joao Santos

Reputation: 33

Excel VBA loop through visible filtered rows

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.

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

Upvotes: 3

Views: 37345

Answers (1)

mtholen
mtholen

Reputation: 1661

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

Upvotes: 10

Related Questions