Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.
share|improve this question
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
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.