Select same cell after sorting
 

I would like to adjust this code to do the following:

Say that a cell in row 25 is selected and then the code is run,
the sort takes place perfectly but the activecell remains in row 25.
Is it possible to have the activecell move with the sorted rows,
thus retaining the original selection?

Sub RectifyError()

Sheets("View Rounds").Unprotect Password:="pinev85"

Dim LRow As Long

LRow = Cells(Rows.Count, 2).End(xlUp).Offset(0, 0).Row

If Intersect(ActiveCell, Range("B3:F2" & LRow)) Is Nothing Then
MsgBox "You must select within the round you would like to correct",
vbCritical
GoTo EarlyClose
End If

Rows("3:" & LRow).Sort Key1:=Cells(3, 4), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

'*******
'Select the cell with the data that was originally selected
'*******


EarlyClose:

Sheets("View Rounds").Protect Password:="pinev85"


End Sub

Select same cell after sorting
 

Put an X into a auxilary column IV in the selected row. then look for the X
to reselect the cell at the end

Sub RectifyError()

Sheets("View Rounds").Unprotect Password:="pinev85"

Dim LRow As Long
Columns("IV").clearcontents
SelectColumn =selection.Column
Range("IV",Selection.Row) = "X"

LRow = Cells(Rows.Count, 2).End(xlUp).Offset(0, 0).Row

If Intersect(ActiveCell, Range("B3:F2" & LRow)) Is Nothing Then
MsgBox "You must select within the round you would like to correct",
vbCritical
GoTo EarlyClose
End If

Rows("3:" & LRow).Sort Key1:=Cells(3, 4), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

'*******
'Select the cell with the data that was originally selected
set c = Columns("IV").find(what:="X",lookin:=xlvalues)
Cells(c.row,SelectColumn).Select
Columns("IV").clearcontents

'*******


EarlyClose:

Sheets("View Rounds").Protect Password:="pinev85"


End Sub


"Sandy" wrote:

I would like to adjust this code to do the following:

Say that a cell in row 25 is selected and then the code is run,
the sort takes place perfectly but the activecell remains in row 25.
Is it possible to have the activecell move with the sorted rows,
thus retaining the original selection?

Sub RectifyError()

Sheets("View Rounds").Unprotect Password:="pinev85"

Dim LRow As Long

LRow = Cells(Rows.Count, 2).End(xlUp).Offset(0, 0).Row

If Intersect(ActiveCell, Range("B3:F2" & LRow)) Is Nothing Then
MsgBox "You must select within the round you would like to correct",
vbCritical
GoTo EarlyClose
End If

Rows("3:" & LRow).Sort Key1:=Cells(3, 4), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

'*******
'Select the cell with the data that was originally selected
'*******


EarlyClose:

Sheets("View Rounds").Protect Password:="pinev85"


End Sub

Select same cell after sorting
 

Sub RectifyError()

Sheets("View Rounds").Unprotect Password:="pinev85"
'==========
mycell = ActiveCell
'==========

Dim LRow As Long


LRow = Cells(Rows.Count, 2).End(xlUp).Offset(0, 0).Row

If Intersect(ActiveCell, Range("B3:F2" & LRow)) Is Nothing Then
MsgBox "You must select within the round you would like to correct",
vbCritical
GoTo EarlyClose
End If

Rows("3:" & LRow).Sort Key1:=Cells(3, 4), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

'*******
'adjust to your column or use cells.find
Columns(1).Find(mycell).Activate
'Select the cell with the data that was originally selected
'*******


EarlyClose:

Sheets("View Rounds").Protect Password:="pinev85"


End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Sandy" wrote in message
...
I would like to adjust this code to do the following:

Say that a cell in row 25 is selected and then the code is run,
the sort takes place perfectly but the activecell remains in row 25.
Is it possible to have the activecell move with the sorted rows,
thus retaining the original selection?

Sub RectifyError()

Sheets("View Rounds").Unprotect Password:="pinev85"

Dim LRow As Long

LRow = Cells(Rows.Count, 2).End(xlUp).Offset(0, 0).Row

If Intersect(ActiveCell, Range("B3:F2" & LRow)) Is Nothing Then
MsgBox "You must select within the round you would like to
correct", vbCritical
GoTo EarlyClose
End If

Rows("3:" & LRow).Sort Key1:=Cells(3, 4), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

'*******
'Select the cell with the data that was originally selected
'*******


EarlyClose:

Sheets("View Rounds").Protect Password:="pinev85"


End Sub