sort

[kenpcli]

I need to sort column A thru Z by column B, but only down to the first empty cell, then skip 3 lines and sort the reminder.

 

[crazydragon84]

should specify more. if you are looking for non-vba way to do it, i don't think its possible.

Sort of manual, but you can have a helper column that has formula =if(value in column B = "", cell above + 1, cell above). This will basically give you incremental numbers each time you have a blank in column B. Then at the end of the list, you can add 2 of each numbers (starting with 2 - whatever max is). Copy and paste value that column. Sort on helper column and then by column B.

 

[kenpcli]

ok so this is what I have:
ActiveWorkbook.Worksheets("CA Site Sub Total").sort.SortFields.Clear
ActiveWorkbook.Worksheets("CA Site Sub Total").sort.SortFields.add Key:=Range("B6:B6"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("CA Site Sub Total").sort
.SetRange Range("a6:Z1000")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

The problem is it sorts the whole document. I need it to sort down to the first empty cell, skip 3 lines then sort again. I have two different sets of data that cant be sorted together or it throws the numbers off.

 

[crazydragon84]

did you write the script or record it? I assume its the latter since you are guessing whether the header is there or not lol

so there is just one blank cell in column B? If so you can store the blank row number
Row_Num = Column("B:B").find(what:="", LookIn:=xlValues, Lookat:=xlWhole).row

Then you can set the sort range A6:Z Row_Num and do a second sort from Row_Num + 1 to last row. Insert 2 rows after Row_Num