Excel 2007 - I have to do extra to get sort yo work, why? 1

[makeitwork09]

I am using Excel 2007

I do not understand why I had to add 1 to the row in order to get it so include the last row in the range in the sort. RowNum is equal to the last row in the range, however, without adding 1 in the statement below, the last row stays where it was even though it was the highest value in the range.

   Dim RowNum As Long

   RowNum = Cells(Cells.Rows.Count, "A").End(xlUp).Row

       Worksheets("CSHomePrice_History").Range("A3:W" & RowNum + 1).Sort Key1:=Range("A3:A" & RowNum + 1), Order1:=xlDescending, _
           Header:=xlYes, MatchCase:=False, _
           Orientation:=xlTopToBottom, SortMethod:=xlPinYin

Thanks

 

[SkipVought]

hi,

    With Worksheets("CSHomePrice_History")
        .Range("A3").Sort _
            Key1:=.Range("A3").CurrentRegion, _
            Order1:=xlDescending, _
            Header:=xlYes, _
            MatchCase:=False, _
            Orientation:=xlTopToBottom, _
            SortMethod:=xlPinYin
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Sorry, I oopsed...

    With Worksheets("CSHomePrice_History").Range("A3")
        .CurrentRegion.Sort _
            Key1:=.Cells(1,1), _
            Order1:=xlDescending, _
            Header:=xlYes, _
            MatchCase:=False, _
            Orientation:=xlTopToBottom, _
            SortMethod:=xlPinYin
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

Thanks Skip

I gave that a try and a few other things, but it sorts row 3. Perhaps I am not understanding the header property of the range object.?

The header is row 3, but when I run the code using what you provided the header row moves to row 2. The desired descending sort works, however. The values being sorted are dates formated as the Month, spelled out, and year.

Thanks P

 

[makeitwork09]

I missed your most recent post btw. My response was to your initial response.

 

[SkipVought]

Then, diagnosing your symptom, you have data in row 2, contiguous with your table, a HUGE NO NO in table design.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

Yes, you are correct, but I didn't design the table. This is how the table comes from the website.

 

[makeitwork09]

Since I don't need those rows they've added I'll add code to remove them, then the code you provided will work. This was a good exercise as it gave me a better understanding of CurrentRegion.

Thanks Skip

 

[SkipVought]

then insert an empty row to isolate the table and hide it...

    With Worksheets("CSHomePrice_History")
        .Range("A3").EntireRow.Insert
        .Range("A3").EntireRow.Hidden = True
        With .Range("A4")
            .CurrentRegion.Sort _
                Key1:=.Cells(1, 1), _
                Order1:=xlDescending, _
                Header:=xlYes, _
                MatchCase:=False, _
                Orientation:=xlTopToBottom, _
                SortMethod:=xlPinYin
        End With
    End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

That did the trick Skip. Thank You