Results 1 to 4 of 4

Thread: VBA going sort crazy
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default VBA going sort crazy

    What is wrong with this?

    lastrow is set (e.g., it's 100), there are headers in row 4.

    The error is that the "sort reference is not valid."

    Code:
    Worksheets("Reformatted").Range("A4:L" & lastrow).Sort Key1:=Range("H4"), Order1:=xlAscending, Header:=xlYes

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,163
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA going sort crazy

    I suspect that the Reformatted sheet is not active, therefore it's trying to use H4 on the active sheet as the sort key. Try
    Code:
    With Worksheets("Reformatted")
         .Range("A4:L" & lastrow).Sort Key1:=.Range("H4"), Order1:=xlAscending, Header:=xlYes
    End with
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    629
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA going sort crazy

    Got the same error! Hmmm.

    Code:
    With Worksheets("Reformatted")
     .Range("A4:L" & lastrow).Sort Key1:=Range("H4"), Order1:=xlAscending, Header:=xlYes
    End With
    Hold on...missed the period in the KEY!! DUH. So tedious. I need better glasses.

    Thanks, Fluff.
    Last edited by kweaver; Sep 12th, 2019 at 01:56 PM.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,163
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA going sort crazy

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •