Select a range based on number next to range

[jakevogel]

Hello, I'm a novice VBA user, and have a macro that I want to tweak, because there's an operation within the macro that can take a ton of time with large files. I'm trying to copy and paste a range of cells, and I'm using a Do Until ... Loop right now.

At this point in the macro, I have a list of SSN's in column H, starting with cell H7, and next to each one, in column I, there is a number indicating how many funds each SSN holds in their portfolio. The list has been sorted by #funds, so all the "1" fund SSN's are at the top. These are the SSN's I want to copy and paste in Column N.

So, to recap: the list of SSN's starts in cell H7, and the list of #funds starts in cell I7. This is what I wrote:

Please Login or Register  to view this content.

So it's looking at the cell in column I, and if it's a "1", it copies the SSN next to it in column H, and pastes it into column N. Then it goes to the next cell in column I, etc. I'm copying and pasting each SSN one at a time.

Can anyone suggest a way for the macro to select all the SSN's with a "1" next to them, and copy/paste in one grand maneuver?

Thank you so much!!!! Jake

[danielexcelvba]

Is it reasonable to assume that the cell after the desired range will contain "2" since it is sorted?

[xladept]

Maybe:

Please Login or Register  to view this content.

BTW - Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[jakevogel]

Yes, the cell after the desired range is "2". Thank you!

[jakevogel]

Thank you for letting me know about code tags. I edited my original post.

I will try your code - thanks so much! Jake

[xladept]

Hey Jake,

I wrote that thing in word - here's a working version-sorry

Please Login or Register  to view this content.

[jakevogel]

That worked perfectly! My biggest list was 7700 lines long, and it took the macro about a minute to run. The same list now took less than two seconds.

Problem solved!

Thank you!!

[xladept]

You're welcome!

*Thanks for the rep!