Data Sort VBA (Tidy Up)

[garypea123]

Hi,

I am looking for a VBA which will data sort dynamic data.

Sheet = Test

Sort by:

1. Name (Column A)
2. BBE (Column B)

(I have recorded a macro which is not tidy as sometimes there could be 4 lines or 20 lines. Also my macro falls down at times because 5 cells below where the data ends there Merged cells which caused the macro script to fail).

Ideally I would like the macro only to look at the data from B3:G3 to where the data ends (not including then the merged text)

 

[bebo021999]

Option Explicit
Sub test()
Dim lr&
lr = Range("B3").End(xlDown).Row
Range("B3:G" & lr).Sort Key1:=Range("B3"), order1:=xlAscending, _
                        Key2:=Range("F3"), order2:=xlAscending, _
                        Header:=xlYes
End Sub

 

[garypea123]

Perfect - Thank you very much.

 

[MARK858]

@garypea123, just another option, but if your data is always consistent with it's layout in your image then you could use currentregion rather than using a variable as per the code below.

As for your merged cells (although it makes no difference to this post), when doing horizontal merging like you are I would recommend looking at Excel tutorial: How to use Center Across Selection in Excel as you'll get far fewer issues long term centering the selection rather than merging the cells.

Sub test2()
Range("B3").CurrentRegion.Sort Key1:=Range("B3"), order1:=xlAscending, _
                        Key2:=Range("F3"), order2:=xlAscending, _
                        Header:=xlYes
End Sub

 

[garypea123]

@garypea123, just another option, but if your data is always consistent with it's layout in your image then you could use currentregion rather than using a variable as per the code below.

As for your merged cells (although it makes no difference to this post), when doing horizontal merging like you are I would recommend looking at Excel tutorial: How to use Center Across Selection in Excel as you'll get far fewer issues long term centering the selection rather than merging the cells.

Sub test2()
Range("B3").CurrentRegion.Sort Key1:=Range("B3"), order1:=xlAscending, _
                        Key2:=Range("F3"), order2:=xlAscending, _
                        Header:=xlYes
End Sub

Thank you, I will take a look also.

Its terms of the Merge of cells. This has not been done by me (I do not like merge personally). However, it is the result of the exports / spreadsheets that are being sent to me. Ideally, for the purpose if this, I prefer not to amend the formatting of the original raw data.