Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving columns with formulas gives #REF! | Excel Worksheet Functions | |||
Moving Columns | Excel Discussion (Misc queries) | |||
Moving columns about | Excel Programming | |||
Moving Columns only | Excel Programming | |||
Moving Columns | Excel Programming |