Using VBA / Macro to autosort pairs of connecting columns and rows?

**Stephen2135** · 02-15-2013, 04:08 PM

I have a series of columns that I want to autosort, selecting from a range of filters. Each pair of columns are connected, so I want to sort columns A and B; then columns C and D. I want the information in columns/rows to sort individually. Columns A is coded Red, Amber and Green, Column B included the connecting information. Columns C again is coded Red, Amber and Green with the connecting information. When I use the sort function it sorts each row of infromation from Column A across.

**JBeaucaire** · 02-16-2013, 10:04 AM

You can highlight any two columns and sort just those two columns.

Sorting by Cell Color is an option in the Sort Wizard in Excel 2010, is that available in Excel 2007?

You can turn on the macro recorder and record it sorting your columns A:B the way you want. Then turn off the macro recorder.

The resulting code could be used to create a looping macro that goes through all the columns on the Activesheet.

Post a workbook if you want more specific help. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Use BEFORE/AFTER sheets in that workbook if that helps make it clearer.

**Stephen2135** · 02-16-2013, 11:03 AM

Thanks for your assistance.

I have tried using autosort and sorting by pairs, but it filters across into the other columns and affects the rows. The filter does not restrict itself to the 2 columns selected.

I have attached an rough example of the workbook I have in mind. I hope to use the paired columns filtering using R (red)A (amber) and G (green) building in suitable macros. The Auto filter selection would be ideal, as it only displays the requitred information but it runs across each row.

TEST DATA - 16 FEBRUARY 2013.xlsx

Many thanks

Stephen

**JBeaucaire** · 02-18-2013, 05:24 PM

The autosorting seems to work fine on the selected columns for autosorting: http://screencast.com/t/fS4TSngTu

**Stephen2135** · 02-18-2013, 07:12 PM

Originally Posted by JBeaucaire

The autosorting seems to work fine on the selected columns for autosorting: http://screencast.com/t/fS4TSngTu

Thanks again, re auto sort apologies for being a bit of a novice at this.

The sort works fine, however what I was hoping to achieve is a selection of data in each column after selecting a specific criteria. For example selecting column A - RED returning only those corresponding entries associated in column B; and selecting RED in column C returning relevant entries in D.

The filter function allows this in part, where I can select RED column A and it filters relevant/corresponding entries in column B. I cannot restrict the filter to only two columns in filters across each row of the worksheet. I would like to apply the filter by pair of columns.

[Desired effect] Filtering only each pair on columns and removing references to other entries identified as Amber or Green.
COLUMN A..........................COLUMN B.............COLUMN C...........COLUMN D .......... COLUMN E........ COLUMN F
RED...................................Test 1....................RED...................Test 6.................. RED............. Test 8
RED...................................Test 2....................RED...................Test 11

[All other entries hidden/removed using filter - for example AMBER and GREEN entries in Columns A and B, C and D and E and F]

I'm not too sure if any of the above is possible, or if it makes sense the way I have put this across. I didn't really explain very well at the start of my question as I referred to Auto sort when I should may be have mentioned filtering out certain information as well.