I have a range (the image below).

screenshot of Excel

Please note that:

  • Range is generated by another application, so number of rows is ever changing, and is not formatted as an Excel 'table'
  • The number of columns, however, is fixed.
  • There is always a Totals row, which is always the last row (cells beyond this however contains formula evaluating to '')

I want help with a VBA Macro that will sort the range using header D as key (ascending):

[See link to the sorted table below]

Please note that:

  • The Totals row (which is always the last row) should be ignored and not sorted.

After this sort, I want the same VBA Macro to apply conditional formatting to the range using the same header D as key - the formatting applies a border around the range where D is the same:

Final table is here: http://goo.gl/H118Lx

Please note that:

  • I don't want border around unique values in header D i.e rows where the cell-value-count is not greater than 1.
  • What have you tried already? Why not go to the developer tab and record a macro, do it manually, and then put something together. We can help you if there are errors or it's not doing something as expected.. – Raystafarian Dec 20 '13 at 20:28
  • I have indeed tried to record a vba to sort but it doesnt ignore the total rows, also doesnt work for the conditional formating, I have researched but can't seem to make it work, help will be appreciated – yinka Dec 20 '13 at 21:14
  • you'll want to use vba to count all rows, subtract one, then sort that number of rows (excluding totals row). Then the formatting will need to be done after finding duplicates. – Raystafarian Dec 20 '13 at 22:29
  • 1
    Questions asking for code must demonstrate a minimal understanding of the problem being solved. Include attempted solutions, why they didn't work, and the expected results. See also: Stack Overflow question checklist – gtwebb Dec 21 '13 at 0:59
  • Related: stackoverflow.com/questions/20586429/… – nixda Dec 23 '13 at 10:42

You don't need VBA at all. You just need a better understanding of the flexibility behind conditional formats. Because of that, I'm going to answer the question even though it's a poor one.

You can sort by column D as is and the totals row will be at the bottom if it's the only one with a blank in it. Apply your conditional formats to the entire column as shown below.

Format: Borders on the left
Range: A:A
Formula: =AND($D1<>"",OR($D1=OFFSET($D1,-1,0),$D1=OFFSET($D1,1,0)))

Format: Borders on the right
Range: E:E
Formula: =AND($D1<>"",OR($D1=OFFSET($D1,-1,0),$D1=OFFSET($D1,1,0)))

Format: Borders on the top
Range: A:E
Formula: =AND($D1<>"",$D1<>OFFSET($D1,-1,0),$D1=OFFSET($D1,1,0))

Format: Borders on the bottom
Range: A:E
Formula: =AND($D1<>"",$D1=OFFSET($D1,-1,0),$D1<>OFFSET($D1,1,0))

This method will turn this sheet:


... into this sheet:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.