Ineffecient Looping - alternatives to coding?

Hi all:

I'm a VBA and programming newbie - if I dare say and am faced with a question that I hope someone can help me think through.

I've read and been told to avoid looping if at all possible since it's slow; however, for my solution I don't think I can get around it since in my worksheet I need to determine if a particular value exist in a column, then copy the value of that row in Column "A" into a new worksheet in the same column "A".

For example:

• I have 2 columns in my worksheet: "A" and "B", where Column "A" is Name, and Column "B" is Payment Made.
• The worksheet for this example is 2000 rows of names. Column "B" value that I'm looking for is the value "Yes".
• If the value of "Yes" is found, copy the value in Column "A" (Name) into a new worksheet in the first column (A) and so on.

So what is the most efficient way to code this without using For I loop, or IF Bx = "Yes", .....etc?

Thanks!

Hi..

Try this..

Please Login or Register  to view this content.

Thanks Holger:

I'll have to read up on this AutoFilter and how code the syntax - not sure if I understand properly, so if I put in some code that will apply a filter such that it will show only rows of column A where the value is "Yes", and then perform the copy of column B to the new worksheet?

I don't even know where to begin to write the VBA code, but will need to put some thought into it.

Melissa

Thanks apo: Sorry didn't see your posting......if you get a chance can you walk me through your sample code to help me understand?

Melissa.

Hi Holger:

A thought came to mind: so if I go into the worksheet and apply a filter on Column "A" to show just rows with "Yes", and it returns 10 filtered rows from 2000 rows that matches "yes", and I run the macro to look for the value "Yes", does that mean that the macro won't necessary have to go through all 2000 rows, but only looks in the 10 filtered rows?

Sorry if this sounds like a silly question.

Appreciate it.
Melissa

Originally Posted by HaHoBe

Hi, MelissaGregory,

narrow the number of recorda down by using Autofilter or using WorksheetFunction.CountIf prior to looping the visible range, using Find/FindNext or Match.

Ciao,
Holger

Thank you Holger:

I think I'm starting to get it, but unfortunately, I still am a bit unclear with a few things - I appreciate any help you or anyone can offer:

1. I'm confused why "1" is minus from the Rows.Count [ie: Resize(.Rows.Count - 1)]
2. What does SpecialCells(12) do and why the number "12"?
3. Why is rng need to be set to "Nothing"?

Sorry for all the questions, but if I can understand more of code, I'll eventually get there.

Thanks!
Melissa

Originally Posted by HaHoBe

Hi, Melissa,

you may take apoīs code and have a go on that one. The following code is adapted and commented so that you should get an image of what the code should be doing:

Please Login or Register  to view this content.

Ciao,
Holger

Hi..

What does SpecialCells(12) do and why the number "12"?

For further info on SpecialCells method..

http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

I use SpecialCells(12) instead of SpecialCells(xlCellTypeVisible) because it is less to type...

Oh boy.....arrays? I'm nowhere there yet, but I guess I can do some research on how to load worksheet cells into it and try. I"m not sure how an array would not be inefficient though.

Holger/apo: thanks for the explanations...I think it's finally clear!

Hi Holger:

I think I fully understand this piece of logic/code now: many thanks to you and all. I tested it and it works....however, I tried to incorporate the code into a worksheet here at work, but I'm getting an error

I believe that it has something to do with Cells "A1" through "C1" in my worksheet, which has text/titles and descriptions, so the data doesn't really begin until "A2", which has the headers and then the data below at "A3"

I tried changing the code:

"With Sheets("Sheet1").Range("A1").CurrentRegion"
to

With Sheets("Sheet1").Range("A2:G1000").CurrentRegion

but I"m still getting an error:

"Run-Time Error "1004"
AutoFilter method of Range Class Failed"

When I remove the entire row beginning in Cell A1, which has the titles/text, the macro works.


Sorry, can you or anyone help? I thought I defined the range correctly, and I don't understand why it's not working.

Melissa

I'm curious: Is there a function that can return the number of rows that meet the AutoFilter criteria?

I just found an issue to this code:

If there are no records found via the AutoFilter, the Macro returns an error message

"Run-Time Error: 1004:" No Cells where found.

Is there any logic that can be built to perhaps display a message saying "No records with Yes in field" and then exit the routine?

Thanks
Melissa

Thanks Holger: you're a star! Works fine

Appreciate it much.
Melissa