VBA to add a border to a report and sort a column

[The_Snook]

Hi,

I've been building a report in which millz has been instrumental in helping me deliver. Link

The final thing I would like to do is to add a border around the outside of the report and sort the 'charge' column (K) descending. I thought I would be able to obtain the code for this by using the macro wizard but this is returning a lot of code, most of which I imagine is unneccessary.

Please note that this report will be used regularly for various searches so I need the code to remove the old border and reapply it to the new data each time a search is run.

Thanks in advance,

Snook

[nilem]

Hi Snook,
try changing this line

ws1.Range("E3:K" & Rows.Count).ClearContents

to this

ws1.Range("E3").CurrentRegion.Offset(1).Clear

and add these lines

...
    End If
Next
Columns("E:K").AutoFit
With Range("E2:K" & Cells(Rows.Count, 5).End(xlUp).Row)
    .Sort Key1:=.Cells(1, 7), Order1:=xlDescending, Header:=xlYes
    .Offset(1).Resize(.Rows.Count - 1).Borders.LineStyle = xlContinuous
End With
Application.ScreenUpdating = True
End Sub

[The_Snook]

Hi nilem,

That adds criss cross borders across all cells in the data range i'd prefer just an outside border. Is that possible?

Thanks

Snook

[nilem]

...
Columns("E:K").AutoFit
With Range("E2:K" & Cells(Rows.Count, 5).End(xlUp).Row)
    .Sort Key1:=.Cells(1, 7), Order1:=xlDescending, Header:=xlYes
    With .Offset(1).Resize(.Rows.Count - 1).Borders
        .Item(xlEdgeLeft).LineStyle = xlContinuous
        .Item(xlEdgeBottom).LineStyle = xlContinuous
        .Item(xlEdgeRight).LineStyle = xlContinuous
    End With
End With
Application.ScreenUpdating = True
End Sub