Excel: Cells as variables in VBA + Range Question

[Benoni2]

I am trying to sort a page in excel. The trick is I want it to sort a range of cells that might change as new rows are added by the user. I am working off of the macro excel wrote when I started sorting.

What I want to do is set the first cell of the range into a variable as well as the last cell of the range which is the one which will change. I am also using an offset to determine what that cell is.

I can't figure out how to make an acceptable variable that refers to a cell.

Secondly, I can seem to get it to except the range using one variable to another.

ActiveWindow.SmallScroll Down:=-3
Dim cell_first As Range
Dim cell_last As Range
cell_first = Range("B5")
cell_last = Range("sortend").Offset(-1, 0)
Range("cell_first":"cell_last").Select

Selection.Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

 

[SkipVought]

Hi,

Check out...

How can I rename a table as it changes size faq68-1331

So if you named your DYNAMIC RANGE, MyTable

   with range("MyTable")
      .Sort Key1:=cells(.row, .Find("MyHeading").column)), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
  end with

assuming that you are sorting on the column heading MyHeading

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Benoni2]

Thanks! I will play aroun with that. That might take care of it just fine.

For my personal knowledge, any idea on the other part of my question? How to name cells as variables in vba?

 

[SkipVought]

]
    [s]ActiveWindow.SmallScroll Down:=-3[/s]
    Dim cell_first As Range
    Dim cell_last As Range
    [b]set[/b] cell_first = Range("B5")
    [b]set[/b] cell_last = Range(cell_first).End(xldown)
    Range(cell_first, cell_last).Select

I avoid using the Select and Activate methods as much as possible...

How Can I Make My Code Run Faster? faq707-4105

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Benoni2]

Thanks for your help on this. This explanation helped me figure out a number of things I was working on. Thanks for your patience.