Need help applying a macro sort to row 1, since we don't use headers.

[wittonlin]

This Sub is part of a large macro, but isolates the problem. It also runs independently as a call, as does the others like SortOHRows, SortMDRows, etc. because I'm not smart enough to compile them into ONE Sub!

Nearly 100% of the time the rows with CA in column F are alphanumerically first. There's only 3 states that are before CA, but are uncommon for us. Hence the problem.

After the macro compiles and edits the file records it sorts the entire file by state (column F). Then there are certain states where individual Subs are called, like the one for CA below, SortCARows, to sort those states now by phone (column C). No matter what I do, the very first record in the raw file with CA in column F sorts up to row 1 and is ALWAYS ignored when the Sub below is called, regardless of the phone number. ALL other Subs for different states always sort all rows, for any given state, by phone (column C).

All I can think of is that Excel thinks the first row is a header row, even though I even changed the code .Header = xlGuess to .Header = xlNo.

Sub SortCARows()
    Dim bottomF As Long
    bottomF = Range("F" & Rows.count).End(xlUp).Row
    Dim FirstRow As Long
    Dim lastrow As Long
    FirstRow = Range("F1:F" & bottomF).Find(What:="CA", LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
    lastrow = Range("F1:F" & bottomF).Find(What:="CA", LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("CurrentLeadFileUsing").Sort.SortFields.Clear
    Sheets("CurrentLeadFileUsing").Sort.SortFields.Add Key:=Range("C" & FirstRow & ":C" & lastrow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With Sheets("CurrentLeadFileUsing").Sort
        .SetRange Range("A" & FirstRow & ":P" & lastrow)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Thanks, in advance, for anyone that can help!

 

[MARK858]

Sheet1 was me messing around and the PERSONAL.xlsb is the binary template I use to save all the macros.

Re-reading your post then you are putting the code in your personal workbook and so the code is looking for a sheet called "CurrentLeadFileUsing" in the personal workbook. This why you are getting the last error.

You either have to prefix the worksheet with the workbook, ActiveWorkbook or use ActiveSheet

Sub SortCARows()
  Dim wks           As Worksheet
  Dim iRowBeg       As Long
  Dim iRowEnd       As Long


  With ActiveSheet
    With .Range("F:F")
      iRowBeg = .Find(What:="CA", After:=.Cells(.Cells.Count), LookAt:=xlWhole, SearchDirection:=xlNext).Row
      iRowEnd = .Find(What:="CA", After:=.Cells(.Cells.Count), LookAt:=xlWhole, SearchDirection:=xlPrevious).Row
    End With
    .Range(.Cells(iRowBeg, "A"), .Cells(iRowEnd, "P")).Sort Key1:=.Cells(iRowBeg, "C"), MatchCase:=False, Header:=xlNo
  End With
End Sub

MS stinks. This has to be a bug. This PC does have Office 2007 on it....
As you can see, I'm using .Header = xlNo ...for what I have NO idea.

Btw. it is not a Bug, it is because you don't understand how Find works. Find has an After parameter which you omitted so it goes to it's default which is the top left cell of the range, in your Case F1 so the code looks from F2 onwards.

In the code shg posted

After:=.Cells(.Cells.Count)

looks at the last cell in the range and so the first cell After (because it loops to the beginning) is cell F1.

 

[wittonlin]

Thank you! Well done. Way better than having to insert a row, run the Sub and remove the row! lol

And also for help understanding using a generic sheet (the active sheet) vs. a specific sheetname!