problem moving columns around
 

All,

I need to re-order some columns in Worksheet. Here is the code which
works just fine (to a point):

......
For fldCnt = 0 To numOfFlds - 1
' Check every name in the first row
'
For colNum = 1 To numOfCols
fldVal = wks.Cells(rowFirst, colNum).Value
If fldList(fldCnt) = fldVal Then
If colNum < (found + 1) Then
' Column needs to be moved to 'curPos'
'
Set rngColToMove = wks.Range(wks.Cells(rowFirst, colNum),
wks.Cells(rowLast, colNum))
Set rngDst = wks.Range(wks.Cells(rowFirst, curPos),
wks.Cells(rowLast, curPos))
Range(rngColToMove.Address).Select
Selection.Cut
Range(rngDst.Address).Select
Selection.Insert (xlShiftToRight)
curPos = curPos + 1
End If
found = found + 1
Exit For
End If
Next colNum
Next fldCnt
......

The problem is that when I move the column the contents of
"wks.Cells(rowFirst, colNum)" does not change to reflect the column's
movement.. In other words, if the original column order is a,b,c,d and
I change it to d,a,b,c what I see in the first row, programmatically,
is still a,b,c,d. It is as though I need some sore of "refresh" after
I do the cut/paste. I tried making it a Range object and then
resetting it after the cut/paste, but the order was still a,b,c,d.

Any ideas?

TIA,

Bill

problem moving columns around
 

Would it be easier to use your macro recorder and copy the columns as you
need and in the order you want them in way out on the spreadsheet (like out
to columns AA, AB, AC, AD, etc...

Then you can just delete the original columns (and all blank columns) to
shift the corrected layout as needed.

Mark

"bill" wrote in message
...
All,

I need to re-order some columns in Worksheet. Here is the code which
works just fine (to a point):

.....
For fldCnt = 0 To numOfFlds - 1
' Check every name in the first row
'
For colNum = 1 To numOfCols
fldVal = wks.Cells(rowFirst, colNum).Value
If fldList(fldCnt) = fldVal Then
If colNum < (found + 1) Then
' Column needs to be moved to 'curPos'
'
Set rngColToMove = wks.Range(wks.Cells(rowFirst, colNum),
wks.Cells(rowLast, colNum))
Set rngDst = wks.Range(wks.Cells(rowFirst, curPos),
wks.Cells(rowLast, curPos))
Range(rngColToMove.Address).Select
Selection.Cut
Range(rngDst.Address).Select
Selection.Insert (xlShiftToRight)
curPos = curPos + 1
End If
found = found + 1
Exit For
End If
Next colNum
Next fldCnt
.....

The problem is that when I move the column the contents of
"wks.Cells(rowFirst, colNum)" does not change to reflect the column's
movement.. In other words, if the original column order is a,b,c,d and
I change it to d,a,b,c what I see in the first row, programmatically,
is still a,b,c,d. It is as though I need some sore of "refresh" after
I do the cut/paste. I tried making it a Range object and then
resetting it after the cut/paste, but the order was still a,b,c,d.

Any ideas?

TIA,

Bill

problem moving columns around
 

On Mar 15, 7:37*pm, "Mark Ivey" wrote:
Would it be easier to use your macro recorder and copy the columns as you
need and in the order you want them in way out on the spreadsheet (like out
to columns AA, AB, AC, AD, etc...

Then you can just delete the original columns (and all blank columns) to
shift the corrected layout as needed.

Mark

"bill" wrote in message

...



All,

I need to re-order some columns in Worksheet. Here is the code which
works just fine (to a point):

.....
* *For fldCnt = 0 To numOfFlds - 1
* * *' Check every name in the first row
* * *'
* * *For colNum = 1 To numOfCols
* * * *fldVal = wks.Cells(rowFirst, colNum).Value
* * * *If fldList(fldCnt) = fldVal Then
* * * * *If colNum < (found + 1) Then
* * * * * *' Column needs to be moved to 'curPos'
* * * * * *'
* * * * * *Set rngColToMove = wks.Range(wks.Cells(rowFirst, colNum),
wks.Cells(rowLast, colNum))
* * * * * *Set rngDst = wks.Range(wks.Cells(rowFirst, curPos),
wks.Cells(rowLast, curPos))
* * * * * *Range(rngColToMove.Address).Select
* * * * * *Selection.Cut
* * * * * *Range(rngDst.Address).Select
* * * * * *Selection.Insert (xlShiftToRight)
* * * * * *curPos = curPos + 1
* * * * *End If
* * * * *found = found + 1
* * * * *Exit For
* * * *End If
* * *Next colNum
* *Next fldCnt
.....

The problem is that when I move the column the contents of
"wks.Cells(rowFirst, colNum)" does not change to reflect the column's
movement.. In other words, if the original column order is a,b,c,d and
I change it to d,a,b,c what I see in the first row, programmatically,
is still a,b,c,d. It is as though I need some sore of "refresh" after
I do the cut/paste. I tried making it a Range object and then
resetting it after the cut/paste, but the order was still a,b,c,d.

Any ideas?

TIA,

Bill- Hide quoted text -

- Show quoted text -

Thanks for the tip. I also decided that was the best way.

Bill

problem moving columns around
 

No problem, glad to help....

I have had to do similar sorting in my own data many times before, and I
have found it is easier just to do it in the aforementioned method than to
work in the same region as the original data.

Mark


"bill" wrote in message
...
On Mar 15, 7:37 pm, "Mark Ivey" wrote:
Would it be easier to use your macro recorder and copy the columns as you
need and in the order you want them in way out on the spreadsheet (like
out
to columns AA, AB, AC, AD, etc...

Then you can just delete the original columns (and all blank columns) to
shift the corrected layout as needed.

Mark

"bill" wrote in message

...



All,

I need to re-order some columns in Worksheet. Here is the code which
works just fine (to a point):

.....
For fldCnt = 0 To numOfFlds - 1
' Check every name in the first row
'
For colNum = 1 To numOfCols
fldVal = wks.Cells(rowFirst, colNum).Value
If fldList(fldCnt) = fldVal Then
If colNum < (found + 1) Then
' Column needs to be moved to 'curPos'
'
Set rngColToMove = wks.Range(wks.Cells(rowFirst, colNum),
wks.Cells(rowLast, colNum))
Set rngDst = wks.Range(wks.Cells(rowFirst, curPos),
wks.Cells(rowLast, curPos))
Range(rngColToMove.Address).Select
Selection.Cut
Range(rngDst.Address).Select
Selection.Insert (xlShiftToRight)
curPos = curPos + 1
End If
found = found + 1
Exit For
End If
Next colNum
Next fldCnt
.....

The problem is that when I move the column the contents of
"wks.Cells(rowFirst, colNum)" does not change to reflect the column's
movement.. In other words, if the original column order is a,b,c,d and
I change it to d,a,b,c what I see in the first row, programmatically,
is still a,b,c,d. It is as though I need some sore of "refresh" after
I do the cut/paste. I tried making it a Range object and then
resetting it after the cut/paste, but the order was still a,b,c,d.

Any ideas?

TIA,

Bill- Hide quoted text -

- Show quoted text -

Thanks for the tip. I also decided that was the best way.

Bill