Link to home
Start Free TrialLog in
Avatar of Rayne
RayneFlag for United States of America

asked on

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
Avatar of Rayne
Rayne
Flag of United States of America image

ASKER

Thank you
Avatar of Norie
Norie

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

Avatar of Rayne

ASKER

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
Avatar of Rayne

ASKER

let me know if you have further questions
Avatar of Rayne

ASKER

not sure whats a quickest way to do this
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rayne

ASKER

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
Avatar of Rayne

ASKER

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 :)
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.
Avatar of Rayne

ASKER

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
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.
Avatar of Rayne

ASKER

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 :)
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.