macro to move columns

victorcarreto

New Member
Joined
Jun 28, 2012
Messages
7
Hello

I need help with my macro, to move columns in my worksheet, basically what I am trying to do is to move column H to Q, column G to P, column F to N, column E to K, column D to J, and column C to H.
Here is my macro, it edit's the content in the of the cells in the column A, C, D, E and F. but now I need to move the columns as I described before...


ANY IDEAS???




Regards


Victor./






Code:
[COLOR=#000000][FONT=Arial]Sub Clean_Data()


Dim LastRow As Long, MyRange As Range, RangeH As Range
Dim C As Range, LA As Long, LB As Long, LC As Long, LD As Long, LE As Long, LF As Long, LG As Long


LA = Cells(Cells.Rows.Count, "A").End(xlUp).Row
LB = Cells(Cells.Rows.Count, "B").End(xlUp).Row
LC = Cells(Cells.Rows.Count, "C").End(xlUp).Row
LD = Cells(Cells.Rows.Count, "D").End(xlUp).Row
LE = Cells(Cells.Rows.Count, "E").End(xlUp).Row
LF = Cells(Cells.Rows.Count, "F").End(xlUp).Row
LG = Cells(Cells.Rows.Count, "G").End(xlUp).Row
LH = Cells(Cells.Rows.Count, "H").End(xlUp).Row


LastRow = WorksheetFunction.Max(LA, LB, LC, LD, LE, LF, LG, LH)


Set MyRange = Range("A1:A" & LastRow)
For Each C In MyRange
    'C.Value = Replace(UCase(Replace(C.Value, "-", "")), ".PAR", "")
    'C.Value = Replace(UCase(Replace(C.Value, "-", "")), ".ASM", "")
    'C.Value = Replace(UCase(Replace(C.Value, "-", "")), ".PSM", "")
    'C.Offset(, 2) = Replace(UCase(C.Offset(, 2)), "KG", "")
    'C.Offset(, 3) = Replace(UCase(C.Offset(, 3)), "MM", "")
    'C.Offset(, 4) = Replace(UCase(C.Offset(, 4)), "MM", "")
    'C.Offset(, 5) = Replace(UCase(C.Offset(, 5)), "MM", "")
Next
[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]
[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]End Sub[/FONT][/COLOR]
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,378
Try something like this. Test it first on a copy of your data.

Code:
[color=darkblue]Sub[/color] Clean_Data()


    [color=darkblue]Dim[/color] vStr [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] vStr [color=darkblue]In[/color] Array("-", ".PAR", ".ASM", ".PSM")
        Columns("A:A").Replace What:=vStr, Replacement:="", LookAt:=xlPart, _
                               SearchOrder:=xlByRows, MatchCase:=[color=darkblue]False[/color]
    [color=darkblue]Next[/color] vStr
    
    Columns("C:C").Replace What:="KG", Replacement:="", LookAt:=xlPart, _
                           SearchOrder:=xlByRows, MatchCase:=[color=darkblue]False[/color]
                           
    Columns("D:F").Replace What:="MM", Replacement:="", LookAt:=xlPart, _
                           SearchOrder:=xlByRows, MatchCase:=[color=darkblue]False[/color]
                           
                    
    [color=green]'Move Columns[/color]
    [color=green]'H to Q, column G to P, column F to N, column E to K, column D to J, and column C to H.[/color]
    [color=darkblue]With[/color] Columns("H:H")
        .Copy Destination:=Columns("Q:Q")
        .ClearContents
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]With[/color] Columns("G:G")
        .Copy Destination:=Columns("P:P")
        .ClearContents
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]With[/color] Columns("F:F")
        .Copy Destination:=Columns("N:N")
        .ClearContents
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]With[/color] Columns("E:E")
        .Copy Destination:=Columns("K:K")
        .ClearContents
    [color=darkblue]End[/color] With
    With Columns("D:D")
        .Copy Destination:=Columns("J:J")
        .ClearContents
    [color=darkblue]End[/color] With
    With Columns("C:C")
        .Copy Destination:=Columns("H:H")
        .ClearContents
    [color=darkblue]End[/color] With
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,612
Members
414,080
Latest member
penguin23

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top