Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When entering data into a range of cells, select the entire range. | Excel Discussion (Misc queries) | |||
Compare a selected Range with a Named range and select cells that do not exist | Excel Programming | |||
Select Range of Cells | Excel Discussion (Misc queries) | |||
select from a range only some cells | Excel Programming | |||
select from a range only some cells | Excel Programming |