Need Help: Moving Left to Column A

  • Thread starter Thread starter JStone0218
  • Start date Start date
J

JStone0218

When the code runs, I end up in cell "AD21". Now, I wanting to scroll left
until I end up in Column A, "A21". The problem, the number of Columns & Rows
vary based on the imported file.

Is there a simple way to do this? I tried using Offset, but since the number
of Columns vary, I need a way for Offset to be dynamic, not static.

Thanks for the help in advance.

James

Sub Macro8()

ActiveSheet.Select
Range("A5").Select

Cells.Find(What:="Grand Total", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Offset(1, 0).Select
Selection.Sort Key1:=ActiveCell, Order1:=xlDescending, Type:=xlSortValues,
_
OrderCustom:=1, Orientation:=xlTopToBottom
ActiveCell.Offset(0, -1).Select
Selection.Sort Key1:=ActiveCell, Order1:=xlDescending, Type:=xlSortValues,
_
OrderCustom:=1, Orientation:=xlTopToBottom

Dim firstBlank As Range
'set a range variable to the first blank
Set firstBlank = ActiveCell.End(xlDown).Offset(1, 0)
'display the cell found
firstBlank.Select

ActiveCell.Offset(0, -30).Select 'tried to use but the number of
columns vary - getting an error

End Sub
 
One way:

ActiveCell.Offset(0, -30).Select
becomes
cells(activecell.row,1).select
or
activecell.entirerow.cells(1).select
or
ActiveCell.Offset(0, 1 - ActiveCell.Column).Select

I like either of the first 2. They're easier to understand when I'm reviewing
the code later on.
 
If you always want to select col a then
cells(activecell.row,"a").select

But, usually selections are not necessary. I don't quite understand what you
are doing.
You don't have to goto a cell to sort. Example
range("a2:x200").sort key1:=range("a2")
 
Back
Top