Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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:

Before

... into this sheet:

After

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .