VBA to shift cells to the left

Katy Jordan

Well-known Member
Joined
Jun 28, 2008
Messages
596
Hi, how can i get this

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 76px"><COL style="WIDTH: 64px"><COL style="WIDTH: 197px"><COL style="WIDTH: 65px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 74px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>WTXMF3</TD><TD style="TEXT-ALIGN: right">1326154</TD><TD>RIO TINTO</TD><TD>AUD</TD><TD>MLWSINBF</TD><TD>SALE </TD><TD style="TEXT-ALIGN: right">258</TD><TD>414.13 ML W/S INT BD FD </TD><TD style="TEXT-ALIGN: right">16-Mar-09</TD><TD style="TEXT-ALIGN: right">17-Mar-09</TD><TD style="TEXT-ALIGN: right">-251307.75</TD><TD>N</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

to this

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 76px"><COL style="WIDTH: 64px"><COL style="WIDTH: 197px"><COL style="WIDTH: 65px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 74px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD>WTXMF3</TD><TD style="TEXT-ALIGN: right">1326154</TD><TD>RIO TINTO</TD><TD>AUD</TD><TD>MLWSINBF</TD><TD>SALE </TD><TD>258 414.13 ML W/S INT BD FD </TD><TD style="TEXT-ALIGN: right">16-Mar-09</TD><TD style="TEXT-ALIGN: right">17-Mar-09</TD><TD style="TEXT-ALIGN: right">-251308</TD><TD>N</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Katy Jordan,

What row on Sheet1 begins the data to move left?

Can we have another screenshot with more sample rows?
 
Upvote 0
Katy Jordan,

What row on Sheet1 begins the data to move left?

Can we have another screenshot with more sample rows?

does this help

Excel Workbook
ABCDEFGHIJKL
6PFOLIOA/C CODEACCT NAME CURCODE DESCRIPTNNARRATIVE DATE ENTRY DATE CASH VALUE De
7--------------------------------------------------------------------------------------------------------------------------------------------
8WTXMF3131131RIO TINTOAUDMLWSINBFADJUSTMEN2385.26 ML W/S INT BD FD 29-Dec-088-Jan-09 N
9WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED 1-Jan-0927-Jan-09 N
10WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED 1-Jan-0927-Jan-09-572.4N
11WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED 1-Feb-0923-Feb-09 N
12WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED 1-Feb-0923-Feb-09-519.62N
13WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED 1-Feb-0924-Feb-09 N
14WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED 1-Feb-0924-Feb-09519.62N
15WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED 1-Feb-0924-Feb-09 N
16WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED 1-Feb-0924-Feb-09-689.22N
17WTXMF3878787RIO TINTOAUDMLWSINBFSALE 490001.26 ML W/S INT BD FD16-Feb-0917-Feb-09-480838.24N
18WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED 1-Mar-0923-Mar-09 N
19WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED 1-Mar-0923-Mar-09-312.37N
20WTXMF34544RIO TINTOAUDMLWSINBFSALE 258414.13 ML W/S INT BD FD16-Mar-0917-Mar-09-251307.75N
21WTXMF3131131RIO TINTOAUDMLWSINBFSALE 273715.73 ML W/S INT BD FD23-Mar-0925-Mar-09-267475.02N
22WTXMF3131131RIO TINTOAUDMLWSINBFSALE 401875.41 ML W/S INT BD FD27-Mar-0931-Mar-09-392672.47N
23WTXMF3131131RIO TINTOAUDCAPWIT CAPITAL CAP WITHD ORD 30-Mar-0931-Mar-09108N
24WTXMF3131131RIO TINTOAUDCAPITL CAPITAL CAP CONTR ORD 30-Mar-0931-Mar-09-0.01N
25WTXMF3131131RIO TINTOAUDCAPWIT CAPITAL CAP WITHD ORD 30-Mar-0931-Mar-090.01N
Sheet1
 
Upvote 0
Why not create a new column after H and put in I7:

=G7 & " " & H7

Drag it completely down. Copy and paste as values. Then removed column G & H.

Is this something that has to happen ofter or just the one time?
 
Upvote 0
If you only wanted the ones that didn't have a date in Column G, I would just sort by Column G so the dates are all together and then use my suggestion on the others.
 
Upvote 0
I think I have seen this problem before. You have some offset cells due to the way the file was brought into excel.

Based on the data you provided, I have this code to combine the cells needed and shift the others. There are a few assumptions like the value "N" in column L.

You should be able to modify it to fit if this isn't correct.

Code:
Sub move_columns()
    Columns("G").Select
    Selection.Insert shift:=xlToRight
    Range("G1:G" & ActiveCell.SpecialCells(xlLastCell).Row).Select
    For Each c In Selection
     If UCase(c.Offset(0, 6).value) = "N" Then
 
      c.Formula = "=H" & c.Row & " & " & Chr(34) & " " & Chr(34) & " & I" & c.Row
      c.copy
      c.PasteSpecial Paste:=xlValues
      c.Offset(0, 2).value = c.Offset(0, 3).value
      c.Offset(0, 3).value = c.Offset(0, 4).value
      c.Offset(0, 4).value = c.Offset(0, 5).value
      c.Offset(0, 5).value = c.Offset(0, 6).value
      c.Offset(0, 6).value = ""
     Else
     c.value = c.Offset(0, 1).value
     End If
    Next c
    Columns("H").Delete shift:=xlLeft
End Sub
 
Upvote 0
Katy Jordan,

Before the macro:


Excel Workbook
ABCDEFGHIJKL
6PFOLIOA/C CODEACCT NAME * * * * * * * *CUR*CODE * **DESCRIPTNNARRATIVE * * * * * * * * ** *DATE * **ENTRY DATE* *CASH VALUE *De
7--------------------------------------------------------------------------------------------------------------------------------------------
8WTXMF3131131RIO TINTOAUDMLWSINBFADJUSTMEN2385.26 ML W/S INT BD FD *29-Dec-088-Jan-09* * * * * *N
9WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Jan-0927-Jan-09* * * * * *N
10WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Jan-0927-Jan-09-572.4N
11WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0923-Feb-09* * * * * *N
12WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0923-Feb-09-519.62N
13WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0924-Feb-09* * * * * *N
14WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0924-Feb-09519.62N
15WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0924-Feb-09* * * * * *N
16WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0924-Feb-09-689.22N
17WTXMF3878787RIO TINTOAUDMLWSINBFSALE * *490001.26 ML W/S INT BD FD16-Feb-0917-Feb-09-480838.24N
18WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Mar-0923-Mar-09* * * * * *N
19WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Mar-0923-Mar-09-312.37N
20WTXMF34544RIO TINTOAUDMLWSINBFSALE * *258414.13 ML W/S INT BD FD16-Mar-0917-Mar-09-251307.75N
21WTXMF3131131RIO TINTOAUDMLWSINBFSALE * *273715.73 ML W/S INT BD FD23-Mar-0925-Mar-09-267475.02N
22WTXMF3131131RIO TINTOAUDMLWSINBFSALE * *401875.41 ML W/S INT BD FD27-Mar-0931-Mar-09-392672.47N
23WTXMF3131131RIO TINTOAUDCAPWIT *CAPITAL *CAP WITHD ORD * * * * * * *30-Mar-0931-Mar-09108N
24WTXMF3131131RIO TINTOAUDCAPITL *CAPITAL *CAP CONTR ORD * * * * * * *30-Mar-0931-Mar-09-0.01N
25WTXMF3131131RIO TINTOAUDCAPWIT *CAPITAL *CAP WITHD ORD * * * * * * *30-Mar-0931-Mar-090.01N
Sheet1



After the macro:


Excel Workbook
ABCDEFGHIJKL
6PFOLIOA/C CODEACCT NAME * * * * * * * *CUR*CODE * **DESCRIPTNNARRATIVE * * * * * * * * ** *DATE * **ENTRY DATE* *CASH VALUE *De
7--------------------------------------------------------------------------------------------------------------------------------------------
8WTXMF3131131RIO TINTOAUDMLWSINBFADJUSTMEN2 385.26 ML W/S INT BD FD *29-Dec-088-Jan-09* * * * * *N
9WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Jan-0927-Jan-09* * * * * *N
10WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Jan-0927-Jan-09-572.4N
11WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0923-Feb-09* * * * * *N
12WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0923-Feb-09-519.62N
13WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0924-Feb-09* * * * * *N
14WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0924-Feb-09519.62N
15WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0924-Feb-09* * * * * *N
16WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0924-Feb-09-689.22N
17WTXMF3878787RIO TINTOAUDMLWSINBFSALE * *490 001.26 ML W/S INT BD FD16-Feb-0917-Feb-09-480838.24N
18WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Mar-0923-Mar-09* * * * * *N
19WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Mar-0923-Mar-09-312.37N
20WTXMF34544RIO TINTOAUDMLWSINBFSALE * *258 414.13 ML W/S INT BD FD16-Mar-0917-Mar-09-251307.75N
21WTXMF3131131RIO TINTOAUDMLWSINBFSALE * *273 715.73 ML W/S INT BD FD23-Mar-0925-Mar-09-267475.02N
22WTXMF3131131RIO TINTOAUDMLWSINBFSALE * *401 875.41 ML W/S INT BD FD27-Mar-0931-Mar-09-392672.47N
23WTXMF3131131RIO TINTOAUDCAPWIT *CAPITAL *CAP WITHD ORD * * * * * * *30-Mar-0931-Mar-09108N
24WTXMF3131131RIO TINTOAUDCAPITL *CAPITAL *CAP CONTR ORD * * * * * * *30-Mar-0931-Mar-09-0.01N
25WTXMF3131131RIO TINTOAUDCAPWIT *CAPITAL *CAP WITHD ORD * * * * * * *30-Mar-0931-Mar-090.01N
Sheet1




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).


Code:
Option Explicit
Sub MoveData()
Dim LR As Long, a As Long
Dim Hold As String
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
For a = 8 To LR Step 1
  Cells(a, 8).Select
  If Not IsDate(Cells(a, 8)) Then
    Hold = ""
    Hold = Cells(a, "G") & " " & Cells(a, "H")
    Cells(a, "G") = Hold
    Range("I" & a & ":L" & a).Copy Range("H" & a)
    Range("L" & a) = ""
  End If
Next a
Range("G8:G" & LR).HorizontalAlignment = xlLeft
Application.ScreenUpdating = True
End Sub


Then run the "MoveData" macro.
 
Upvote 0
Hi Hiker

the code did not work on this line
Excel Workbook
12121RIO TINTOAUDINT0005 SALE 39727.08 INTECH BAL GR FD C29-Dec-082-Jan-09-27690.6N
Sheet
Katy Jordan,

Before the macro:


Excel Workbook
ABCDEFGHIJKL
6PFOLIOA/C CODEACCT NAME * * * * * * * *CUR*CODE * **DESCRIPTNNARRATIVE * * * * * * * * ** *DATE * **ENTRY DATE* *CASH VALUE *De
7--------------------------------------------------------------------------------------------------------------------------------------------
8WTXMF3131131RIO TINTOAUDMLWSINBFADJUSTMEN2385.26 ML W/S INT BD FD *29-Dec-088-Jan-09* * * * * *N
9WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Jan-0927-Jan-09* * * * * *N
10WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Jan-0927-Jan-09-572.4N
11WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0923-Feb-09* * * * * *N
12WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0923-Feb-09-519.62N
13WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0924-Feb-09* * * * * *N
14WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0924-Feb-09519.62N
15WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0924-Feb-09* * * * * *N
16WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0924-Feb-09-689.22N
17WTXMF3878787RIO TINTOAUDMLWSINBFSALE * *490001.26 ML W/S INT BD FD16-Feb-0917-Feb-09-480838.24N
18WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Mar-0923-Mar-09* * * * * *N
19WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Mar-0923-Mar-09-312.37N
20WTXMF34544RIO TINTOAUDMLWSINBFSALE * *258414.13 ML W/S INT BD FD16-Mar-0917-Mar-09-251307.75N
21WTXMF3131131RIO TINTOAUDMLWSINBFSALE * *273715.73 ML W/S INT BD FD23-Mar-0925-Mar-09-267475.02N
22WTXMF3131131RIO TINTOAUDMLWSINBFSALE * *401875.41 ML W/S INT BD FD27-Mar-0931-Mar-09-392672.47N
23WTXMF3131131RIO TINTOAUDCAPWIT *CAPITAL *CAP WITHD ORD * * * * * * *30-Mar-0931-Mar-09108N
24WTXMF3131131RIO TINTOAUDCAPITL *CAPITAL *CAP CONTR ORD * * * * * * *30-Mar-0931-Mar-09-0.01N
25WTXMF3131131RIO TINTOAUDCAPWIT *CAPITAL *CAP WITHD ORD * * * * * * *30-Mar-0931-Mar-090.01N
Sheet1



After the macro:


Excel Workbook
ABCDEFGHIJKL
6PFOLIOA/C CODEACCT NAME * * * * * * * *CUR*CODE * **DESCRIPTNNARRATIVE * * * * * * * * ** *DATE * **ENTRY DATE* *CASH VALUE *De
7--------------------------------------------------------------------------------------------------------------------------------------------
8WTXMF3131131RIO TINTOAUDMLWSINBFADJUSTMEN2 385.26 ML W/S INT BD FD *29-Dec-088-Jan-09* * * * * *N
9WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Jan-0927-Jan-09* * * * * *N
10WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Jan-0927-Jan-09-572.4N
11WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0923-Feb-09* * * * * *N
12WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0923-Feb-09-519.62N
13WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0924-Feb-09* * * * * *N
14WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0924-Feb-09519.62N
15WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0924-Feb-09* * * * * *N
16WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Feb-0924-Feb-09-689.22N
17WTXMF3878787RIO TINTOAUDMLWSINBFSALE * *490 001.26 ML W/S INT BD FD16-Feb-0917-Feb-09-480838.24N
18WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Mar-0923-Mar-09* * * * * *N
19WTXMF3131131RIO TINTOAUDAUDCUSTREINVESTINTEREST RECEIVED * * * * *1-Mar-0923-Mar-09-312.37N
20WTXMF34544RIO TINTOAUDMLWSINBFSALE * *258 414.13 ML W/S INT BD FD16-Mar-0917-Mar-09-251307.75N
21WTXMF3131131RIO TINTOAUDMLWSINBFSALE * *273 715.73 ML W/S INT BD FD23-Mar-0925-Mar-09-267475.02N
22WTXMF3131131RIO TINTOAUDMLWSINBFSALE * *401 875.41 ML W/S INT BD FD27-Mar-0931-Mar-09-392672.47N
23WTXMF3131131RIO TINTOAUDCAPWIT *CAPITAL *CAP WITHD ORD * * * * * * *30-Mar-0931-Mar-09108N
24WTXMF3131131RIO TINTOAUDCAPITL *CAPITAL *CAP CONTR ORD * * * * * * *30-Mar-0931-Mar-09-0.01N
25WTXMF3131131RIO TINTOAUDCAPWIT *CAPITAL *CAP WITHD ORD * * * * * * *30-Mar-0931-Mar-090.01N
Sheet1




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).

Code:
Option Explicit
Sub MoveData()
Dim LR As Long, a As Long
Dim Hold As String
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
For a = 8 To LR Step 1
  Cells(a, 8).Select
  If Not IsDate(Cells(a, 8)) Then
    Hold = ""
    Hold = Cells(a, "G") & " " & Cells(a, "H")
    Cells(a, "G") = Hold
    Range("I" & a & ":L" & a).Copy Range("H" & a)
    Range("L" & a) = ""
  End If
Next a
Range("G8:G" & LR).HorizontalAlignment = xlLeft
Application.ScreenUpdating = True
End Sub


Then run the "MoveData" macro.
 
Upvote 0
Mooseman, some of the cells are shifted to Col M, i changed n to m but it didnt work

I think I have seen this problem before. You have some offset cells due to the way the file was brought into excel.

Based on the data you provided, I have this code to combine the cells needed and shift the others. There are a few assumptions like the value "N" in column L.

You should be able to modify it to fit if this isn't correct.

Code:
Sub move_columns()
    Columns("G").Select
    Selection.Insert shift:=xlToRight
    Range("G1:G" & ActiveCell.SpecialCells(xlLastCell).Row).Select
    For Each c In Selection
     If UCase(c.Offset(0, 6).value) = "N" Then
 
      c.Formula = "=H" & c.Row & " & " & Chr(34) & " " & Chr(34) & " & I" & c.Row
      c.copy
      c.PasteSpecial Paste:=xlValues
      c.Offset(0, 2).value = c.Offset(0, 3).value
      c.Offset(0, 3).value = c.Offset(0, 4).value
      c.Offset(0, 4).value = c.Offset(0, 5).value
      c.Offset(0, 5).value = c.Offset(0, 6).value
      c.Offset(0, 6).value = ""
     Else
     c.value = c.Offset(0, 1).value
     End If
    Next c
    Columns("H").Delete shift:=xlLeft
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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
Back
Top