Select cells from a variable position

  • Thread starter Thread starter Alberto Ast
  • Start date Start date
A

Alberto Ast

If I am in any cells how do I select same cell plus right cell plus cell thee
spots to the right... my current cell location might vary.
Thanks,
 
ActiveCell.Resize(1, 5).Select

Note: most times there is no need to select a range in order to work on it.

ActiveCell.Resize(1, 5).Interior.ColorIndex = 3


Gord Dibben MS Excel MVP
 
Thanks for your input... but what if I need to select two separate ranges at
the same time.... Resize only pick up a continuous range.

For instance I need to select cells on col A,B,F,G,J on a specific row
 
Use Application.Union to combine several cells into a single Range
object:


Dim RR As Range
Dim RowNum As Long
RowNum = ActiveCell.Row
Set RR = Application.Union( _
Cells(RowNum, "A"), Cells(RowNum, "C"), Cells(RowNum, "E"))
RR.Select

Of course, all the cells must be on the same worksheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
It works thanks

Chip Pearson said:
Use Application.Union to combine several cells into a single Range
object:


Dim RR As Range
Dim RowNum As Long
RowNum = ActiveCell.Row
Set RR = Application.Union( _
Cells(RowNum, "A"), Cells(RowNum, "C"), Cells(RowNum, "E"))
RR.Select

Of course, all the cells must be on the same worksheet.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
Click to expand...
 
One more question... this is what I wrote

RowNum = ActiveCell.Row
Set RR = Application.Union(Cells(RowNum, "A"), Cells(RowNum, "B"), _
Cells(RowNum, "D"), Cells(RowNum, "E"), Cells(RowNum, "F"), _
Cells(RowNum, "G"), Cells(RowNum, "I"), Cells(RowNum, "J"))
RR.ClearContents

But as you can see there are several consecutive cells... is there a way to
simplify the comand?

Thanks
 
This single line of code should do what your post code does...

Intersect(ActiveCell.EntireRow, Range("A:B,D:G,I:J")).ClearContents
 
you got it best.... excellent.
Thanks...

Rick Rothstein said:
This single line of code should do what your post code does...

Intersect(ActiveCell.EntireRow, Range("A:B,D:G,I:J")).ClearContents
Click to expand...
 
Back
Top