Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Last Cell that Has Data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Last Cell that Has Data
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Last Cell that Has Data
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Last Cell that Has Data
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Last Cell that Has Data
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help finding the last cell w/data | Excel Worksheet Functions | |||
Formula finding last cell in row containing data | Excel Worksheet Functions | |||
Formula for finding next cell with data? | Excel Discussion (Misc queries) | |||
Finding matching cell data | Excel Worksheet Functions | |||
Finding last cell with data in a column | Setting up and Configuration of Excel |