Need help to find the last row of data

  • Thread starter Thread starter gmangad
  • Start date Start date
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
 
In your case I quess that would be

Sub SelectRows()
Range("A1:B" & Range("A65536").End(xlUp).Row).Select
End Sub
 
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
 
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
 
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...
 
Back
Top