Results 1 to 6 of 6

Thread: Variable in Ranges
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Variable in Ranges

    Among many inexplicable problems is this. I recorded a macro to sort a spreadsheet using Column A as the first key. The pertinent code that Excel generated is

    Code:
     
            ActiveWorkbook.Worksheets("CurrentWines").Sort.SortFields.Add Key:=Range( _
            "A2:A237"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
    The problem is the range is fixed not allowing for new entries. I Added a cell at the bottom and attempted to modify the code by using a variable NOR for the bottom row reference as follows (NOR returns correct value)




    Code:
    Dim NOR as Long ‘NOR = Number of rows in Column A
    
                     NOR = Range("A2", Range("A1").End(xlDown)).Count
    
       
                     ActiveWorkbook.Worksheets("CurrentSheet").Sort.SortFields.Add Key:= _
        Range(Cells(2,1),Cells(NOR,1) ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    
                     xlSortNormal
    Can anyone tell me why it will not sort the last cell?
    David ElGinzo



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

    Default Re: Variable in Ranges

    Hi & welcome to MrExcel
    How about
    Code:
       Dim NOR As Long
    
       With ActiveWorkbook.Worksheets("CurrentSheet")
          NOR = .Range("A" & Rows.Count).End(xlUp).Row
    
          .Range("A1:A" & NOR).Sort Key1:=.Range("A1"), Order1:=xlAscending, DataOption1:= _
             xlSortNormal, Header:=xlYes
       End With
    This assumes you have a header row in row1
    - 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 DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,271
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Variable in Ranges

    Try this

    Code:
      Dim nor As Long
      nor = Range("A" & Rows.Count).End(xlUp).Row
      ActiveWorkbook.Worksheets("CurrentSheet").Sort.SortFields.Add Key:=Range( _
            "A2:A" & nor), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
    Regards Dante Amor

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Variable in Ranges

    Quote Originally Posted by Fluff View Post
    Hi & welcome to MrExcel
    How about
    Code:
       Dim NOR As Long
    
       With ActiveWorkbook.Worksheets("CurrentSheet")
          NOR = .Range("A" & Rows.Count).End(xlUp).Row
    
          .Range("A1:A" & NOR).Sort Key1:=.Range("A1"), Order1:=xlAscending, DataOption1:= _
             xlSortNormal, Header:=xlYes
       End With
    This assumes you have a header row in row1
    !st I would like to thank everyone for their generosity in trying to help me. It appears as if Fluff is the moderator and has a duplicate post. That code works for the last row in Column A shifting the value in "A", NOR but not whole Row NOR. I think I just have to modify the range of Row NOR. I still don't know why operational difference between your code and mine since the value of NOR is the same.

    DanteAmor, Thanks to you also but I cannot get your code to work altho it is very similar to Fluff's. I must be missing something.

    Thanks again to all

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,271
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Variable in Ranges

    Quote Originally Posted by DavidElGinzo View Post
    DanteAmor, Thanks to you also but I cannot get your code to work altho it is very similar to Fluff's. I must be missing something.
    My code is the same as yours, I just added the row.
    But you didn't put all your code to check it.

    Yours:
    Code:
        ActiveWorkbook.Worksheets("CurrentWines").Sort.SortFields.Add Key:=Range( _
            "A2:A237"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
    Mine:
    Code:
      ActiveWorkbook.Worksheets("CurrentSheet").Sort.SortFields.Add Key:=Range( _
            "A2:A" & nor), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal

    You see the difference?


    So if your code works, mine works, but in your full macro.


    ----------------

    Try this

    Select sheet a run this code:
    Code:
    Sub test2()
    Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Sort key1:=Range("A1"), order1:=xlAscending, Header:=xlYes
    End Sub
    Regards Dante Amor

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

    Default Re: Variable in Ranges

    Quote Originally Posted by DavidElGinzo View Post
    That code works for the last row in Column A shifting the value in "A", NOR but not whole Row NOR. I think I just have to modify the range of Row NOR.
    Do you mean that you have other columns that are not getting sorted along with col A?
    - 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
  •