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!

 

[Roderick_E]

firstrow = Columns("F").Find("*", SearchDirection:=xlNext, SearchOrder:=xlByRows, LookIn:=xlValues).Row
lastrow = Columns("F").Find("*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlValues).Row

Those simply look for populated rows regardless of "CA" you are looking for

 

[wittonlin]

I'm not sure I understand. CA, OH, MD, etc. in column F are the conditions where we need to then sort by phone in column C. The only trouble is that after the full file sort by Column F to group the states, whatever was the first record in the file with CA in column F and we run the Sub above to sort that selection by phone, in column C, that first record now in row 1 is always ignored and never sorted; unlike all the other groups of rows with OH, MD, etc. in column F that are all sorted by phone, in column C, properly.

 

[wittonlin]

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.

I already knew the answer, but for fun I added a header (it's difficult to add because of all the ways the full macro manipulates the data) and sure enough it worked perfectly! Argg...

I have no idea how to get Excel in this case NOT to ignore row 1. I even tested manually and just in case I removed the "My data has headers" checkbox. It must lie here, ".SetRange Range("A" & FirstRow & ":P" & lastrow)" ... with someway to trick Excel into including row 1 in this particular sort!

 

[shg]

If CurrentLeadFileUsing is not the active sheet when the code executes, you have lots of unqualified range references that are to the active sheet.

 

[shg]

Try ...

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

  Set wks = Worksheets("CurrentLeadFileUsing")
  
  With wks
    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

 

[wittonlin]

This is ironic! I was going to ask you about the filename, and how I can use something generic/flexible like ActiveSheet?

And, I can't get past the filename line. Set wks = Worksheets("CurrentLeadFileUsing") "Run-time error '9': Subscript out of range."

It doesn't matter but the file name is exact and as a text file. CurrentLeadFileUsing.txt

 

[MARK858]

Count how many sheets from the left Worksheets("CurrentLeadFileUsing") is and change the 1 in the code below to that number and then run the code below.

Copy and paste in the thread what appears in the immediate window (Copy and paste not manually retype).

Sub Testit()
Debug.Print "< &"; Sheets([COLOR="#FF0000"][B]1[/B][/COLOR]).Name; "& >"
End Sub

 

[wittonlin]

Hey Mark! It just so happens how our macro works it creates individual files like CurrentLeadFileUsing.txt, CurrentLeadFileUsing2.txt, etc. and each one is it's own workbook with only one sheet the same name as the filename; so I guess the number is actually zero, but it errors using 0. Using 1, I see the output below, that was in the "Intermediate" section," though it wouldn't write/print to a file because I have all the printer/networking stuff disabled on this laptop.

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

< &CurrentLeadFileUsing& >
< &PERSONAL& >
< &Sheet1& >
< &CurrentLeadFileUsing& >

 

[MARK858]

Have you put the code in the personal workbook and not in a module in the workbook containing the sheet?
If you have then put the code shg posted in a regular module in the actual workbook and run it to see if it works (after adding the red period below).

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

  Set wks = Worksheets("CurrentLeadFileUsing")
 
  With wks
    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
    [SIZE=3][COLOR="#FF0000"][B].[/B][/COLOR][/SIZE]Range(.Cells(iRowBeg, "A"), .Cells(iRowEnd, "P")).Sort Key1:=.Cells(iRowBeg, "C"), MatchCase:=False, Header:=xlNo
  End With
End Sub

though it wouldn't write/print to a file because I have all the printer/networking stuff disabled on this laptop.

I didn't ask for it to be printed to anywhere else other than the immediate window and Debug.Print only prints to the Immediate window anyway.