Select range of cells
 

Can you please tell me how to select cells in row 2 through the last active
cell in the active column. For example, if my cursor is in a cell in column
J, I'd like cells J2:last active cell to be selected so that I can do some
find and replace, copy, etc on those cells. I'm having a hard time getting
the J2. Thank you so much.

Select range of cells
 

To answer your question, this code will select from Row 2 down to the last
used row in the ActiveCell's column...

ACcolumn = ActiveCell.Column
Range(Cells(2, ACcolumn ), Cells(Rows.Count, ACcolumn ).End(xlUp)).Select

However, given your stated goals, you should know you don't need to first
select a range to do something to it... you can work directly with the
range. Perhaps this previous posting of mine (a response to another person
using Select/Selection type constructions) will be of some help to you in
your future programming...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever

you can almost always do this instead...

Range("A1").<whatever

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.

--
Rick (MVP - Excel)


"Alex" wrote in message
...
Can you please tell me how to select cells in row 2 through the last
active
cell in the active column. For example, if my cursor is in a cell in
column
J, I'd like cells J2:last active cell to be selected so that I can do some
find and replace, copy, etc on those cells. I'm having a hard time
getting
the J2. Thank you so much.

Select range of cells
 

Sub RestofRow()
Dim lRow As Long, lCol As Long
With ActiveSheet
lRow = ActiveCell.Row
lCol = ActiveCell.Column
.Range(.Cells(lRow, lCol), .Cells(lRow, .Cells(lRow,
..Columns.Count).End(xlToLeft).Column)).Select
End With
End Sub

--

Regards,
Nigel




"Alex" wrote in message
...
Can you please tell me how to select cells in row 2 through the last
active
cell in the active column. For example, if my cursor is in a cell in
column
J, I'd like cells J2:last active cell to be selected so that I can do some
find and replace, copy, etc on those cells. I'm having a hard time
getting
the J2. Thank you so much.

Select range of cells
 

Thanks a million!

"Rick Rothstein" wrote:

To answer your question, this code will select from Row 2 down to the last
used row in the ActiveCell's column...

ACcolumn = ActiveCell.Column
Range(Cells(2, ACcolumn ), Cells(Rows.Count, ACcolumn ).End(xlUp)).Select

However, given your stated goals, you should know you don't need to first
select a range to do something to it... you can work directly with the
range. Perhaps this previous posting of mine (a response to another person
using Select/Selection type constructions) will be of some help to you in
your future programming...

Whenever you see code constructed like this...

Range("A1").Select
Selection.<whatever

you can almost always do this instead...

Range("A1").<whatever

In your particular case, you have this...

Range("C2:C8193").Select 'select cells to export
For Each r In Selection.Rows

which, using the above concept, can be reduced to this...

For Each r In Range("C2:C8193").Rows

Notice, all I have done is replace Selection with the range you Select(ed)
in the previous statement and eliminate the process of doing any
Select(ion)s. Stated another way, the Selection produced from
Range(...).Select is a range and, of course, Range(...) is a range... and,
in fact, they are the same range, so it doesn't matter which one you use.
The added benefit of not selecting ranges first is your active cell does not
change.

--
Rick (MVP - Excel)


"Alex" wrote in message
...
Can you please tell me how to select cells in row 2 through the last
active
cell in the active column. For example, if my cursor is in a cell in
column
J, I'd like cells J2:last active cell to be selected so that I can do some
find and replace, copy, etc on those cells. I'm having a hard time
getting
the J2. Thank you so much.

Select range of cells
 

Alex,

This is another way of doing the same thing but with a validation to maybe
save you some time in your code.

If you have to do multiple changes inside a range of cells (as you mentioned
J2 through J22351 (or whatever) then one way to go about it is to check to
see if what you need to change is even there in the first place. If at least
1 example is found then your find / replace will proceed, if not your code
will move on to whatever you have next.

I kept the MsgBox in the code (for troubleshooting purposes) but they are
commentated out as they are not needed.

Sub TryThis

Dim CountIfAnswer as Long
Dim rToSearch as Range

CountIfAnswer = WorksheetFunction.CountIf(Range("J:J").EntireColum n,
"<your search here")

If CountIfAnswer = 1 Then
Set rToSearch = Range(Cells(2, 10), Cells(Rows.Count, 10).End(xlUp))

rToSearch.Replace What:="<your search here", Replacement:="<what
you need here", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'MsgBox (CountIfAnswer)
Else
' MsgBox ("Nothing Found")
End If

End Sub

What €˜Set rToSearch does is force Excel to only do a find / replace inside
a range of cells that you are specify vs letting excel iterate through each
cell 1 by 1 until it finds something (A1, A2, A3, B1, B2, B3, etc etc). This
forces Excel to search only where you want it (J2, J3, J4, J5, €¦).

If you dont want to perform the check first, omit the €˜CountIfAnswer part
and the reference to the 'If .. Then' statements and the code will work.

Hope this helps,

Jason

"Alex" wrote:

Can you please tell me how to select cells in row 2 through the last active
cell in the active column. For example, if my cursor is in a cell in column
J, I'd like cells J2:last active cell to be selected so that I can do some
find and replace, copy, etc on those cells. I'm having a hard time getting
the J2. Thank you so much.