Select range of cells

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

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.
 
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.
 
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
 
Thanks a million!

Rick Rothstein said:
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.
Click to expand...
 
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").EntireColumn,
"<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 don’t 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
 
Back
Top