Rayne
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
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
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?
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
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
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
ASKER
let me know if you have further questions
ASKER
not sure whats a quickest way to do this
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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 :)
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.
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.
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
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.
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 :)
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.
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.
ASKER