MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Problem moving several columns of data to the right by one column


Posted by Edward C on May 31, 2001 7:35 AM

The following is a question I posted on 5/24. Someone asked for further explaination. I am basically trying to identify seveal columns of data and then once I have identified them, to select them and move them to the right ( or shift them to the right ) by one column.
Thanks
Edward

Hi,
I am trying to use the offset command to move a user identified region ( set of entire columns ) over to the right by one column. I have broken the code into three mains steps labeled 1, 2, and 3. Step three is where my problem is. i cannot figure out the correct syntax for taking two separate ranges and using them to define a new rectangular range and once I have done that to select that range and move it to the right by one column.
Thanks
Edward

Here is what I do and how I do it:
First I declare several ranges which get used to store the first column and last column in:
Dim hCell As Range
Dim lCol As Range
Dim fCol As Range
Dim bNone As Boolean

Then i do the following steps
1 - identify first column of region
this is done by the following code which searchs to the right for the column with the heading which the user input into a textbox on my userform
' first search for column heading which matches the one
' entered into the text box by the user
bNone = True
For Each hCell In ActiveSheet.Rows(1).Cells
If hCell.Value = txtVoltageDataToNormalize.Value Then
Set fCol = hCell
bNone = False
Exit For
End If

2- then i search to find the first empty column which is the right side of the region
' search for first empty column

bNone = True
For Each hCell In ActiveSheet.Rows(1).Cells
If IsEmpty(hCell) Then
'hCell.Select
Set lCol = hCell
bNone = False
Exit For
End If

3- then i use the offset command to move the region between the two colmuns by one column.
' select the range contained within the first and last columns identified above

'Range(fCol.Address:lCol.Address).EntireColumn.Select
Range(Columns(fCol.Column):Columns(lCol.Column)).EntireColumn.Select

Selection.Offset(0,1).Value


Posted by Russell on May 31, 2001 8:52 AM

Try this:

Range(fCol, lCol).EntireColumn.Select
Selection.Cut Destination:=Range(fCol.Offset(0, 1), lCol.Offset(0, 1)).EntireColumn

Posted by Francisco on May 31, 2001 4:33 PM


Perhaps I haven't understood what you are trying to do, but doesn't merely inserting a column before the column identified as the first one do what you need?
fCol.EntireColumn.Insert