Finding Last Cell that Has Data

[caldog]

I have search through prior posting of how to find last row, then sort from
that information, But I must be doing something wrong becasue I still get a
error message. Also the first cell will always be 'A3'.

Here is the code that I have put together:

Private Sub cmdSort_Click()
Dim rngToSort As Range
Dim LastRow As Long
Dim wks As Worksheet

Set wks = Sheets("XYZ")
Set rngToSort = Range("A3",LastRow = Cells(Rows.Count, "A").End(xlUp).Row)
As Range

rngToSort.Sort Key1:=Range("A3"), order1:=xlAscending, Header:=xlNo

End Sub

Can somebody show me what I'm doing wrong.

Steve

[Jim Thomlinson]

I assume you know how many columns wide you are sorting. I also assume that
your data populates all the way to the bottom of column A (if not then we
need to get a bit more fancy)... You just need to change the "??" to
whichever is your last column...

Private Sub cmdSort_Click()
Dim rngToSort As Range
Dim wks As Worksheet

Set wks = Sheets("XYZ")
Set rngToSort = Range("??3",Cells(Rows.Count, "A").End(xlUp))

rngToSort.Sort Key1:=Range("A3"), order1:=xlAscending, Header:=xlNo

End Sub

--
HTH...

Jim Thomlinson


"caldog" wrote:

> I have search through prior posting of how to find last row, then sort from
> that information, But I must be doing something wrong becasue I still get a
> error message. Also the first cell will always be 'A3'.
>
> Here is the code that I have put together:
>
> Private Sub cmdSort_Click()
> Dim rngToSort As Range
> Dim LastRow As Long
> Dim wks As Worksheet
>
> Set wks = Sheets("XYZ")
> Set rngToSort = Range("A3",LastRow = Cells(Rows.Count, "A").End(xlUp).Row)
> As Range
>
> rngToSort.Sort Key1:=Range("A3"), order1:=xlAscending, Header:=xlNo
>
> End Sub
>
> Can somebody show me what I'm doing wrong.
>
> Steve

[caldog]

Jim,

After re-reading your post, I realize that I did leave out some information,
which would have been helpful to you.

I am using a userform, which has three fill in boxes. The content goes in
Col A,B,C. After all data is added, by way of a add command button, then on
this same userform, I have my sort button. This is where my troubles start
with my sorting code.

My data will just continue to be added in next empty cell in column A.
Therefore, A3 is the starting point, but Col C will change every time I add
new data.

I also changed my coding to match yours, but the sort didn't happen.

Steve

[Jim Thomlinson]

The code that I presented relies on the fact the the range A3:C100 is the
same block of cells as the range A100:C3. Give this a try. I missed that the
code never referenced the worksheet that is defined.

Private Sub cmdSort_Click()
Dim rngToSort As Range
Dim wks As Worksheet

Set wks = Sheets("XYZ")
with wks
Set rngToSort = .Range(.range("C3"), .Cells(Rows.Count,
"A").End(xlUp))
rngToSort.Sort Key1:= .Range("A3"), order1:=xlAscending, Header:=xlNo
end with

End Sub


--
HTH...

Jim Thomlinson


"caldog" wrote:

> Jim,
>
> After re-reading your post, I realize that I did leave out some information,
> which would have been helpful to you.
>
> I am using a userform, which has three fill in boxes. The content goes in
> Col A,B,C. After all data is added, by way of a add command button, then on
> this same userform, I have my sort button. This is where my troubles start
> with my sorting code.
>
> My data will just continue to be added in next empty cell in column A.
> Therefore, A3 is the starting point, but Col C will change every time I add
> new data.
>
> I also changed my coding to match yours, but the sort didn't happen.
>
> Steve
>

[caldog]

Thanks Jim for the help

Steve

"Jim Thomlinson" wrote:

> The code that I presented relies on the fact the the range A3:C100 is the
> same block of cells as the range A100:C3. Give this a try. I missed that the
> code never referenced the worksheet that is defined.
>
> Private Sub cmdSort_Click()
> Dim rngToSort As Range
> Dim wks As Worksheet
>
> Set wks = Sheets("XYZ")
> with wks
> Set rngToSort = .Range(.range("C3"), .Cells(Rows.Count,
> "A").End(xlUp))
> rngToSort.Sort Key1:= .Range("A3"), order1:=xlAscending, Header:=xlNo
> end with
>
> End Sub
>
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "caldog" wrote:
>
> > Jim,
> >
> > After re-reading your post, I realize that I did leave out some information,
> > which would have been helpful to you.
> >
> > I am using a userform, which has three fill in boxes. The content goes in
> > Col A,B,C. After all data is added, by way of a add command button, then on
> > this same userform, I have my sort button. This is where my troubles start
> > with my sorting code.
> >
> > My data will just continue to be added in next empty cell in column A.
> > Therefore, A3 is the starting point, but Col C will change every time I add
> > new data.
> >
> > I also changed my coding to match yours, but the sort didn't happen.
> >
> > Steve
> >