VBA Code for Sorting by Subset

[jardenp]

I have reports that are formatted like so:

A:A

Vendor: [Vendor A]
Item 1
Item 2
Item 3
Vendor: [Vendor B]
Item 1
Item 2
Vendor: [Vendor C]
Item 1
...
1) the number of items per vendor varies
2) the vendor lines are always in bold
3) the item lines always begin with "??-" (as it would be phrased in the Find function)

I want to automatically sort the items in numerical order by vendor. How can I include this in my macro?

 

[Jonmo1]

Setup a 2nd column to put the vendor name in.

so that's column A
In B1 put put Vendor1's name
In B2 put the formula
=IF(LEFT(A2,3)="??-",B1,A2)
Fill down

Now you have each vendor's name in column B.
highlight and copy column B
paste special - values

Now sort by column B.

 

[jardenp]

Thanks

Thanks for the reply jonmo1. I think I understand what your doing and I think that would work if I was to manipulate the sheet in front of me, but how would that translate into VBA code? I'm making a macro for a coworker, so I won't be able to manipulate anything manually--I need it all to work through the single macro.

I do appreciate your reply and if you could help with the VBA code, that would be great too!

JP

 

[Jonmo1]

Re: Thanks

The macro recoreder is your friend...

Click Tools - Macro - Record New Macro
Give it a name and click OK

Now do the steps described above by hand.

Click Tools - Macro - Stop Recording.


You now have a macro that should get you started.
You can copy / paste that resulting code into the thread here, and it can likely be cleaned up a bit.