Reputation: 85
I will try to illustrate the scenario by only focus on the problem and take out all the non essential issues/scripts.
User selects multiple non-contiguous cells. VBA would do a few things...then insert a column. If the selected cells happened to be at the right side of the columns, the content would move one column right of the original selected cells. I need to re-select the cells with the original content before exit sub.
For instance,
I have considered a few methods:
Offset the entire selection to one column right.
selection.offset(0,1).select
This is not a good solution as "A1" would move to "B1". It is only ok if the user selected cells are all at the right side of the inserted column.
Put each cell of selection (selected range) into array. Change the affected cells' range. and use vba to select them all again. The problem is that the vba I wrote can't select the entire array of ranges (multiple non contiguous cells) at once. It only selects the last cell in the array. Here is the summarized code:
Sub mtArea()
Dim Cell, Rg, sRg() As Range
Dim h, i, j, k, noCell, Cnt As Long
Set Rg = Selection
noCell = Rg.Cells.Count
k = 0
' assign each cell in selection to a specific array
If Rg.Areas.Count > 1 Then
ReDim sRg(noCell)
For Each Cell In Rg
k = k + 1
Set sRg(k) = Cell
Next Cell
End If
' select the new located cells
For i = 1 To noCell
If sRg(i).Column > 5 Then ' assuming insert column is "E"
h = 1
Else
h = 0
End If
sRg(i).Offset(0, h).Select
Next i
End Sub
In this case, only the last cell in the original range is being selected. Is there a way to select the entire sRg() range array?
I also hope to explore this way:
Dim Rg as Range
Set Rg = Selection
When user selects multiple non contiguous cells, is there a way for vba to change the individual cells range location in the Rg variable?
What should be the method?
Thank you.
Upvotes: 0
Views: 565
Reputation: 96753
If you assign a Name to the range, the cells will be adjusted after the column is inserted:
Sub RememberTheCells()
Range("A1,C3,D4:E6").Select
Selection.Name = "Previous"
Columns("B:B").Insert Shift:=xlToRight
Range("Previous").Select
MsgBox Selection.Address
End Sub
Upvotes: 0
Reputation: 53137
Try this
Sub InsertDemo()
InsertAndAdjustSelection 2
End Sub
Sub InsertAndAdjustSelection(Col As Long)
Dim strAddress() As String
Dim i As Long
' Save adresses of selected cells
strAddress = Split(Selection.Address, ",")
' Insert Column
Columns(Col).Insert
' Unpdate saved addresses
For i = 0 To UBound(strAddress)
If Range(strAddress(i)).Column >= Col Then
strAddress(i) = Range(strAddress(i)).Offset(, 1).Address
End If
Next
' Select range
Range(Join(strAddress, ",")).Select
End Sub
Upvotes: 0