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