Macro won't select proper cells

ungoliath

Board Regular
Joined
Oct 21, 2004
Messages
113
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.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

craig.penny

Well-known Member
Joined
May 8, 2009
Messages
656
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

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

You can do the same for counting the columns.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
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
Code:
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.

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,595
Messages
5,625,711
Members
416,129
Latest member
karthickerfolg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top