?
Solved

Store in Variant or other Structure

Posted on 2012-09-03
14
Medium Priority
?
476 Views
Last Modified: 2012-09-03
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
0
Comment
Question by:Rayne
  • 8
  • 2
  • 2
  • +1
14 Comments
 

Author Comment

by:Rayne
ID: 38361202
Thank you
0
 
LVL 35

Expert Comment

by:Norie
ID: 38361212
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
 

Author Comment

by:Rayne
ID: 38361231
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Rayne
ID: 38361232
let me know if you have further questions
0
 

Author Comment

by:Rayne
ID: 38361238
not sure whats a quickest way to do this
0
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 38361247
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
 

Author Comment

by:Rayne
ID: 38361263
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
 

Author Comment

by:Rayne
ID: 38361278
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38361292
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
 

Author Comment

by:Rayne
ID: 38361304
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38361336
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
 

Author Comment

by:Rayne
ID: 38361351
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38361373
:-)
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 38361912
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question