Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA: Column Select then Data Select then return to cell A1 | Excel Discussion (Misc queries) | |||
Select all columns with data for sorting. | Excel Discussion (Misc queries) | |||
NEED VBA TO SELECT A CELL; NOTE THE CELL VALUE;COPYADJ CELL;FIND CELL VALUE IN A RANGE AND SO ON | Excel Programming | |||
Using formulas to select cells (Ex: Select every nth cell in a col | Excel Discussion (Misc queries) | |||
Select Case for Sorting / Grouping Columns | Excel Programming |