Macro won't select proper cells

[ungoliath]

A. For whatever reason I can't get this macro to sort the top cell of the selection.

Sub sortPOC()

Application.ScreenUpdating = False

Sheets("POC Information").Activate
Project_Columns = Application.WorksheetFunction.CountA(Range("i3:xfd3"))
Active_Rows = Application.WorksheetFunction.CountA(Columns("C:C"))
With ActiveWorkbook.Worksheets("POC Information").Sort
.SetRange Range(Cells(3, 2), Cells(Active_Rows + 5, Project_Columns + 8))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

Cells B2 and B3 are merged for title purposes if that makes a difference.

B. How do I specify I want the sort to sort by column C:C or the header "Last Name" (Cells C2 and C3 merged)?

C. How do I post the spreadsheet? I can't find any way to add a file.

 

[craig.penny]

What version of Excel are you using? I ask because I use 2007 and I know that's different from previous versions. Have you tried recording a macro while you select this range and sort on column C?

I don't know how you're using this but doesn't CountA just return the number of non empty cells? If there are any empty cells, this won't grab your entire range. This is something I use for counting used rows

Dim LR As Long: LR = ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row

You can do the same for counting the columns.

 

[AlphaFrog]

When C2 and C3 are merged, C3 is considered empty. If Cells 2 and 3 are all merged for each of your columns, then this line will count zero

Project_Columns = Application.WorksheetFunction.CountA(Range("i3:xfd3"))

Also, VBA errors if your headers are merged.

I would suggest you define your sort range starting from row 4 (no merged cells) and specify that range as having no headers.

Sub sortPOC()

    Dim Project_Columns As Long, Active_Rows As Long

    With Sheets("POC Information")
        Project_Columns = .Cells(4, Columns.Count).End(xlToLeft).Column
        Active_Rows = .Cells(Rows.Count, 3).End(xlUp).Row
        .Range("B3", .Cells(Active_Rows, Project_Columns)).Sort _
            Key1:=Range("C3"), _
            Order1:=xlAscending, _
            Header:=xlGuess, _
            OrderCustom:=1, _
            MatchCase:=False, _
            Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    End With
End Sub

Cannot post a file. See signature below for tools to posts data examples.