Need help to find the last row of data

G

gmangad

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
 
P

polletje

In your case I quess that would be

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

polletje

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
 
P

Patrick Molloy

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
 
T

Trevor Shuttleworth

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 said:
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
Click to expand...
 
Top