Variable in Ranges

DavidElGinzo · Aug 17, 2019

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

Fluff · Aug 17, 2019

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

DanteAmor · Aug 17, 2019

Try this

Code:

  Dim nor As Long
  nor = Range("A" &[COLOR=#008000] Rows.Count).End(xlUp[/COLOR]).Row
  ActiveWorkbook.Worksheets("[COLOR=#ff0000]CurrentSheet[/COLOR]").Sort.SortFields.Add Key:=Range( _
        "A2:A" & nor), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal

DavidElGinzo · Aug 18, 2019

Fluff said:

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

DanteAmor · Aug 18, 2019

DavidElGinzo said:
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[COLOR=#ff0000]" & nor[/COLOR]), 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

Fluff · Aug 18, 2019

DavidElGinzo said:
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?

Variable in Ranges

DavidElGinzo

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

DanteAmor

Well-known Member

DavidElGinzo

New Member

DanteAmor

Well-known Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Variable in Ranges

DavidElGinzo

New Member

Excel Facts

Fluff

MrExcel MVP, Moderator

DanteAmor

Well-known Member

DavidElGinzo

New Member

DanteAmor

Well-known Member

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock