Select region

[Simon Woods]

Hi

I've not got used to programming excel. I'm trying to select the bottom
right cell and then select the current region in order to do a sort of the
first three columns without headers.

I'm obviously getting it wrong!!!

Here's my code, but instead of getting the bottom right I've been getting A4
as the data range I want to sort will always start there. When I execute
this, just row 1 gets hilighted and then the sort call gets thrown out.

m_oSheet.Cells.Range("A4").Select
m_oSheet.Cells.Range("A4").Activate

With m_oSheet.Cells.CurrentRegion
.Select
.Activate
.Sort Key1:=Range("C4"), Order1:=xlAscending, _
Key2:=Range("B4"), Order2:=xlAscending, _
Key3:=Range("A4"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:=xlSortNormal
End With

Thanks

Simon

 

[Tom Ogilvy]

Dim rng as Range, bottomright as Range
m_oSheet.Range("A4").Select
With m_oSheet
set rng = .Range("A4").CurrentRegion
set bottomright = rng(rng.count)
set rng = .Range("A4",bottomright)

rng.Sort Key1:=.Range("C4"), Order1:=xlAscending, _
Key2:=.Range("B4"), Order2:=xlAscending, _
Key3:=.Range("A4"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
End With

 

[Simon Woods]

Thanks Tom

Dim rng as Range, bottomright as Range
m_oSheet.Range("A4").Select
With m_oSheet
set rng = .Range("A4").CurrentRegion
set bottomright = rng(rng.count)
set rng = .Range("A4",bottomright)

rng.Sort Key1:=.Range("C4"), Order1:=xlAscending, _
Key2:=.Range("B4"), Order2:=xlAscending, _
Key3:=.Range("A4"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
End With