Store in Variant or other Structure

Hello All,

I have a issue. I have a lot of rows. What I need:
There is a flag column myCol_Flag, that has 1 in certain rows, so the vba will get the row numbers for those flagged with 1 and store in a variant array or some structure and then I will be able to do something like

For n = LBound(varData, ) To UBound(varData,)
      Do something to the row
Next n
The flagged rows in this example are 6,7,14,18
So I will need to go through only the flagged rows to do my stuffff

How do I set up the loop to take into account only the flagged row number?
variantArray.xlsx
RayneAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
The main question is, I think anyway, how are you going to get the row numbers in the first place?

Off the top of my head the only way I can think of would be to find the first occurence using find, then use find again to get the second occurence, then use it again for the third, then the fourth...

So you end up looping but in a slightly different way.

Just thought of another way - filter the data so only rows with 1 in that column are visible.

Then you can grab all the rows using SpecialCells and loop through them

Only problem there is that SpecialCells has a limit of 8912 cells, so would break if there were more than 8192 1s.
0
 
RayneAuthor Commented:
Thank you
0
 
NorieVBA ExpertCommented:
Do you really need to store the row numbers?

How are you going to get them?

Why not loop through the column?

If you find a cell with 1 do whatever you do with the row?

For I = 6 To Range("E" & Rows.Count).End(xlUp).Row

    If Range("F" & I).Value Then

          ' do something with row I
   End IF
Next I

Open in new window

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
RayneAuthor Commented:
Hello  imnorie :)
Thank you for the quick reply, so here is the issue:

I am dealing with 30000 rows, so i am looking for the fastest way to get this done:
What already have a R1C1 thing that basically flags the rows with 1. Now what I need to do in that column -

1) wherever there is 1 in that column- grab the row index  for that row and store it in a variant or a structure.

2) Once I have those row indexes stored, i need to loop through those indexes and do stuff.

looping through 30000 rows for that column can take a while, so if I could only get the row indexes of the flagged rows, and then looping through only those rows might make it faster
0
 
RayneAuthor Commented:
let me know if you have further questions
0
 
RayneAuthor Commented:
not sure whats a quickest way to do this
0
 
RayneAuthor Commented:
Hello Imnorie,
My replies inline..

I asked quite a relevant question, I think anyway, how are you going to get the row numbers in the first place?
I think I will use like rw.row ? Maybe..not sure


Just thought of another way - filter the data so only rows with 1 in that column are visible.
That looks awesome, so use a auto-filter to get the job done.



Then you can grab all the rows using SpecialCells and loop through them
It would be great if would demonstrate that, I heard of  special cells thing before but not done it

Thank you
0
 
RayneAuthor Commented:
Actually I imnorie, looping through the column is not bad at all,

I am doing
for each cel in Rng
 cel.value = A + cel.offset (x,y)

1.4 secounds to go through 30000  - very decent :)
0
 
GrahamSkanRetiredCommented:
Rayne,

Could you consider posting your Excel questions in the Excel zone, instead of the Office zone, please?

It often isn't obvious from the question that it is unambiguously an Excel question, so some of us Office generalists will open the question just in case. With Excel, there are usually specialists around who will be able to respond more rapidly and more appropriately.
0
 
RayneAuthor Commented:
Hello GrahamSkan,

Actually I used to that exactly-  months back but I would not get quick response and delayed responses which made it look like there are less experts eying that zone, but whenever I post it in office, I always gets quick responses and people are there to help out fast
0
 
Saqib Husain, SyedEngineerCommented:
I do not think so Rayne. May be you have had some incidental problem. Otherwise you should get pretty good response from excel alone for problems specific to excel. It is really annoying when someone opens a zone and finds questions more relevant to some other zone.
0
 
RayneAuthor Commented:
Thank Ssaqibh and Graham,

Ok, I will test your idea and start posting excel question in that target zone :)

I guess now we are on the same table :)
0
 
Saqib Husain, SyedEngineerCommented:
:-)
0
 
GrahamSkanRetiredCommented:
Many of us Office specialists have preferences with regard to questions about to which application they can best respond.

Don't worry about posting questions in the Excel zone. Excel excels - excuse the pun (probably intended to be unavoidable by MS) - with its own willing specialists.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.