Code that will select cells to the right of multiple selected cells, but not include the original selected cells.

[jmr9642]

Hello,

I am trying to write code that will select cells to the right of multiple selected cells, but not include the original selected cells. I have A8:A22 selected and am currently using the below code but it only returns one line across from the first row. If this could be replicated through the rest of the range I would have what I need.

Sub AWS_CTI()
Sheets("pivot table current").Select
Range(Range("A8"), Range("A8").End(xlDown)).Select
Range(Cells(Selection.Row, 2), Cells(Selection.Row, 16)).Select
End Sub

The code I have selects rows B8 through B16, which is what I want, but it doesn't select the rest of the range. It just does for A8 instead of doing it for A8 through A22.

Essentially, I have A8:A22 selected, and I want code that will take that selection, and move it one column to the right then enlarge the selection to the end of the data. How can i adjust or add to my code to make this happen?

Thank you for your time in advance.

 

[Joe4]

Welcome to the Board!

So, in your example, if you have A8:A22 selected, you want the code to select B8:B22, right?

Will you selections only ever be one column, or could they be multiple columns?
If multiple, how should that work?
Let's say that you have A8:B22 selected. Do you want the code to select B8:C22, or C8:D22?

Note, if it is only one column, and my assumptions above are correct, then just do this:
Selection.Offset(0, 1).Select
which moves the current selection over one column to the right.

 

[jmr9642]

I want the code to initially select B8:B22, but then I want to extend the selection to the last portion of data moving to the right.

So if there are 16 columns of data, I would want the selection to select B8:B22 and all data to Q8:Q22

Offset just moves the data selection initially, but what I am looking for is for the macro to pick up how many sets of data there are, moving to the right and change the selection to that.

 

[jmr9642]

I figured it out. This is the code I was looking for
Selection.CurrentRegion.Offset(0, 1).Resize(, 1).Select

 

[Joe4]

Try this:

Sub MoveRange()

    Dim rng As Range
    Dim firstCol As Long
    Dim lastCol As Long
   
'   Capture original selected range
    Set rng = Selection
   
'   Find first column selected
    firstCol = ActiveCell.Column
   
'   Find last populated column in first selected cell's row
    lastCol = Cells(ActiveCell.Row, Columns.Count).End(xlToLeft)
   
'   Move range one column to right, and expand range to last column
    rng.Resize(, lastCol - firstCol).Offset(0, 1).Select
   
End Sub