Need help to find the last row of data
 

Hello
I need someone to help me to write a macro to find the last row in my
data.
I will have two columns A & B with text in it.
Suppose I have data in 5 row, 6th row will be empty. When I run the
macro I want the data to be selected A1:B5. If I enter another item in
my data and run the macro, it should select A1:B6. That is the first
step, then I want the data to be sorted on Column A. I hope this is
clear . I am desperately looking someone to help me.
Thanking you
GT


---
Message posted from http://www.ExcelForum.com/

Need help to find the last row of data
 

GT

range("A1:A" & range("A65536").End(xlUp).Row).Select

Regards

Trevor


"gmangad " wrote in message
...
Hello
I need someone to help me to write a macro to find the last row in my
data.
I will have two columns A & B with text in it.
Suppose I have data in 5 row, 6th row will be empty. When I run the
macro I want the data to be selected A1:B5. If I enter another item in
my data and run the macro, it should select A1:B6. That is the first
step, then I want the data to be sorted on Column A. I hope this is
clear . I am desperately looking someone to help me.
Thanking you
GT


---
Message posted from http://www.ExcelForum.com/

Need help to find the last row of data
 

In your case I quess that would be

Sub SelectRows()
Range("A1:B" & Range("A65536").End(xlUp).Row).Select
End Sub


---
Message posted from http://www.ExcelForum.com/

Need help to find the last row of data
 

To be complete on your question:

Sub SelectRowsAndSortOnA()

Range("A1:B" & Range("A65536").End(xlUp).Row).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub


---
Message posted from http://www.ExcelForum.com/

Need help to find the last row of data
 

Thanks to everyone who helped me.. it worked..
Thanks a lot


---
Message posted from http://www.ExcelForum.com/

Need help to find the last row of data
 

I suggest the following adjustment...

Sub SelectRowsAndSortOnA()

WITH Range("A1:B" & Range("A65536").End(xlUp).Row)
.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess
.Name = "MyData"
END WITH
End Sub

I dropped the .Select method.
Having named the range, one can more easily use the table in other code

--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"polletje " wrote in message
...
To be complete on your question:

Sub SelectRowsAndSortOnA()

Range("A1:B" & Range("A65536").End(xlUp).Row).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub


---
Message posted from http://www.ExcelForum.com/

Need help to find the last row of data
 

Not even sure we need to worry about the range. I'm sure that the Sort will
pick the correct range provided you refer to a cell within the range,
Hence:

Sub SortOnA()
Range("A1").Sort _
Key1:=Range("A1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End Sub

Seems to work OK in my brief test.

If there is definitely a header we should declare "Header:= xlYes," or, if
there's not, "Header:=xlNo,"

Regards

Trevor


"Patrick Molloy" wrote in message
...
I suggest the following adjustment...

Sub SelectRowsAndSortOnA()

WITH Range("A1:B" & Range("A65536").End(xlUp).Row)
.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess
.Name = "MyData"
END WITH
End Sub

I dropped the .Select method.
Having named the range, one can more easily use the table in other code

--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"polletje " wrote in message
...
To be complete on your question:

Sub SelectRowsAndSortOnA()

Range("A1:B" & Range("A65536").End(xlUp).Row).Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub


---
Message posted from http://www.ExcelForum.com/