Selecting a range based on cell value

[alpha1980]

Hi,

Please can anybody advise me how (in VBA) to select a range within a column where all values are the same?

For example, given the following:

A B
1 ID Value
2 1 A
3 2 A
4 3 A
5 4 B
6 5 B
7 6 C

How would I select range(B2:B4) where all values = A?

Thanks.

[Andy Pope]

Something like this,

Please Login or Register  to view this content.

[alpha1980]

Thanks Andy. That's brilliant.

What I ultimately need to do is copy the rows containing each range to a new workbook with the same header.

So I would end up with 3 files that look at follows:

A B
1 ID Value
2 1 A
3 2 A
4 3 A

and

A B
1 ID Value
5 4 B
6 5 B

and

A B
1 ID Value
7 6 C

I've been playing around with your code but to no avail. I was trying to offset the range to broaden the selection in order to copy it but no joy.

I'd really appreciate any guidance.

Thanks again.

[Andy Pope]

Is the information in column A and B only?

It maybe better to use Autofilter and copy+paste.

[alpha1980]

Is the information in column A and B only?

It maybe better to use Autofilter and copy+paste.

Really?

Somebody already kindly provided me with an Autofilter solution but I didn't quite understand how to extend it so I went in search of a simpler option.

Perhaps I should have been more persistant.

If you are interested, I can post a link providing I am allowed to link to other sites here?

[StephenR]

It might be quicker to use AutoFilter. Perhaps attach a small sample of your data?

[Andy Pope]

Yes you are allowed to link to other sites. It is actively encouraged that those who cross post include links to other posts.

Please Login or Register  to view this content.

[alpha1980]

Thanks very much guys.

Here is what the sample data looks like.

\1

Column "D" is the key one - I need to split this file by "Code".

The thing that's beating me is the header rows - there are actually 3 but you can't see here because 2 are merged.

The data begins at "A4" and extends across to "AA" but the sample doesn't stretch that far.

Andy - that code above would be great if i knew which ranges to edit so it works with my sample.

Thanks again.

[Andy Pope]

Try this,

Please Login or Register  to view this content.

For future reference it's best to state as much relevant information in your original question rather than reveal vital information during the post.

[alpha1980]

Thanks Andy.

For some reason, the code seems to be generating files containing the top record only.

I'm guessing it is something I am doing.

[Andy Pope]

can you post example data file rather than an image of it.

[alpha1980]

can you post example data file rather than an image of it.

Here you go...

[Andy Pope]

Combination of code change and you having merged cells in the table header.
If you get rid of the merged cells so the headers are in row 2 and data starts in row 3 this will work.

Please Login or Register  to view this content.

[alpha1980]

Thanks once again Andy.

I see the code works but not with the 40,000 records I am trying to sort!

Is this likely to be due to a limitation of AdvancedFilter? Or something else?

I'm learning quickly that I probably should have mentioned the large amount of data at the beginning too...

Edit - ignore that nonsense - it's working fine.

[alpha1980]

Okay, I have made the following changes to Andy's code:

• Added Application.GetOpenFileName to select source file.
• Rather than use Workbooks.Add to create a new workbook, I've created a template .xls that already contains the headers and is called for every iteration of the for loop.
• The template .xls is renamed with the name of the cell D5 ( i.e autofilter result), saved and closed for each iteration of the for loop.

All I need to do now is remove the header (Row 2) so it is not copied to the template along with the data.

Please can anybody help?

Please Login or Register  to view this content.

[Andy Pope]

Add an offset of 1 row to the range being copied.

Please Login or Register  to view this content.