How to loop through filtered advanced filter range ?

[jaryszek]

Hi

i have code where i am using:

Please Login or Register  to view this content.

so xlCellTypeVisible is for loop in an range.

My questions are :

1. is there any faster way of looping through visible cells in advanced filter?
2. How to loop only through specific columns (like with ColumnIndex = 4) in visible cells?


I was thinking to make the array from visible cells but it is working on all data, not only visible ones.

Please help,
Jacek

[romperstomper]

You could simply filter the data to another range somewhere, then load that into an array.

[sintek]

I was thinking to make the array from visible cells but it is working on all data, not only visible ones.

Upload sample file and will show you...

[jaryszek]

Thank you Rory,

it seems easy solution but i am stubborn and want to get this in place and didn't find it on web.
Plus i had to do some changes in created array and replace it with source range....(like removing rows)

Sintek,
i attached the workbook.

[jindon]

Please Login or Register  to view this content.

[sintek]

So what is output...Just want to loop columns(4) if ABC = F2G2H2 OR UPDATE Col 4 values

[jaryszek]

Thanks jindon,

Sintek --> yes just want to loop through column 4.

Best,
Jacek

[sintek]

Then why want to filter and then loop...
Just loop each cell and see if Col A-C matches criteria and if so then do Whatever?
Unless you are wanting something else which you have not specified...

[jindon]

2. How to loop only through specific columns (like with ColumnIndex = 4) in visible cells?

Please Login or Register  to view this content.

[jaryszek]

Thank you jindon,
Sintek just output cellValue to the console

Best,
Jacek

[jaryszek]

I think there are 3 options:

either creating array as jindon showed either loop through visible cells either copying into new worksheet.

But copying into new worksheet and array will not work for my goal which is also removing rows.
So i have to do this inside already existing range.

Visible cells for now is staying.

Once again, thank you very much Guys!!!

Best Wishes!
Jacek

[sintek]

But copying into new worksheet and array will not work for my goal which is also removing rows.
So i have to do this inside already existing range.

You have still not clearly explained what it is you are ultimately wanting to achieve as you have not provided an expected outcome...
You just say loop through filtered cells...

Sintek just output cellValue to the console

What is this console?
Now you say you want to remove rows...
So what is it you actually want?

[jindon]

jaryszek,

I feel like you don't want me to reply to your thread, so don't worry no more reply from me.

[jaryszek]

Thank you Guys.

Sintek,

let me show you expected output in workbook attached.

jindon,

i love your methods and already know it from our previous posts. thank you so much for that!

But in this case maybe i was not clear.
If i will filter and put rows in the array i will not able to remove rows/ change something there.

Let me add workbook as attachment with expected output.

[sintek]

Seems jindon can read your mind so I'm very certain he will give you a solution...

[jaryszek]

Please check file sintek i showed expected output as you wanted.
I hope it is clear now.

Best,
Jacek

[sintek]

yes most certainly, however, you do not explain the steps to get the result...
What has that file and output got to do with your initial filter and then having rows removed...Where oh where is the code supposed to get the name change...

DnsZoneId was changed to DnsZoneId2

was delated because of "DnsZone" string inside AttributeName

I mean what does above even mean...

Like I say...Mind Reader I am not...jindon has already offered two solutions
The second answers your initial

How to loop through filtered advanced filter range

Both still don't achieve what you are actually wanting...

this is Table where i will change/remove rows

This table is blank????

You are either going to have to hard code all the required changes or store these changes in a sheet...
So...Go back to drawing board and explain step by step what it is you are wanting this code to do...

[jaryszek]

I want to :

1) filter SourceData table per "Table2" using advance filter or whatever but IN PLACE.
2) loop through filtered rows and if AttributeName ="DnsZoneId" change it to "DnsZoneId2"
3) Remove row in filtered range where AttributeName = "DnsZone"

Thanks for helping,
Jacek

[sintek]

So IF ABC = F2G2H2 then
If D = DnsZone then delete row
If D = DnsZoneId then change to DnsZoneId2

Please Login or Register  to view this content.