LineEmUp and Sorting issue

[DresdenStudent]

Hi All,

For a project I need to sort two lists of genes with each other. I found a script online that now works for my data set, but I have an issue with the header. I have added an extra header row, that now is continuously sorted with my data list. Does anyone have a suggestion?

The code is:

Sub CompareGenes()
'Author:    Jerry Beaucaire
'Date:      10/27/2010
'Summary:   Align codes in columns C and D removing all D values not in C
Dim i As Long, LR As Long
Application.ScreenUpdating = False


'Last row with data in column F
    LR = Range("F" & Rows.Count).End(xlUp).Row


'Sort both section so numbers are ascending in E and F
    Columns("A:E").Sort Key1:=Range("A3"), order1:=xlAscending, _
        Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    Columns("F:J").Sort Key1:=Range("F3"), order1:=xlAscending, _
        Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal


'line up remaining items so A lines up with E
    i = 2
    Do
        If Cells(i, "A") > Cells(i, "F") And Cells(i, "F") > "" Then
            Cells(i, "A").Resize(1, 5).Insert xlShiftDown
        ElseIf Cells(i, "A") < Cells(i, "F") And Cells(i, "E") > "" Then
            Cells(i, "F").Resize(1, 5).Insert xlShiftDown
        End If
        i = i + 1
    Loop Until Cells(i, "A") = "" And Cells(i, "F") = ""


Application.ScreenUpdating = True
End Sub

 

[Fluff]

What row does your data start in?

 

[DresdenStudent]

The data starts in row 3.

To make it more complicated (well, I don't know how to solve it), the sorting of the data also messes up my formulas.
I want to extract the value in E3-J3, E4-J4, etc. Due to the inserted rows, the formula is messed up and I get for example E25-J34. I have tried making the references absolute, $E$2-$J$2, but this made no difference.

 

[Fluff]

If you make this change

'line up remaining items so A lines up with E
    i = [COLOR=#ff0000]3[/COLOR]
    Do

It will stop the header getting moved. But I don't know how to preserve the formulae

 

[DresdenStudent]

Unfortunately it did not solve the header problem. Actually it did not have any right away noticeable effect.
The second line header I can also work without, although it would have made things a little clearer. The formulae issue that I have now discovered actually is much more of a problem '-.-

 

[DresdenStudent]

However, thanks for your help so far!