Page 1 of 2 12 LastLast
Results 1 to 10 of 13

VBA to shift cells to the left

This is a discussion on VBA to shift cells to the left within the Excel Questions forums, part of the Question Forums category; Hi, how can i get this Sheet1 A B C D E F G H I J K L 7 ...

  1. #1
    Board Regular
    Join Date
    Jun 2008
    Location
    Oslo
    Posts
    596

    Default VBA to shift cells to the left

    Hi, how can i get this

    Sheet1

    ABCDEFGHIJKL
    7WTXMF31326154RIO TINTOAUDMLWSINBFSALE 258414.13 ML W/S INT BD FD 16-Mar-0917-Mar-09-251307.75N


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

    to this

    Sheet1

    ABCDEFGHIJK
    12WTXMF31326154RIO TINTOAUDMLWSINBFSALE 258 414.13 ML W/S INT BD FD 16-Mar-0917-Mar-09-251308N


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

  2. #2
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    11,201

    Default Re: VBA to shift cells to the left

    Katy Jordan,

    What row on Sheet1 begins the data to move left?

    Can we have another screenshot with more sample rows?
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  3. #3
    Board Regular
    Join Date
    Jun 2008
    Location
    Oslo
    Posts
    596

    Default Re: VBA to shift cells to the left

    Quote Originally Posted by hiker95 View Post
    Katy Jordan,

    What row on Sheet1 begins the data to move left?

    Can we have another screenshot with more sample rows?
    does this help

    Sheet1

     ABCDEFGHIJKL
    6 PFOLIO A/C CODE ACCT NAME                 CUR  CODE      DESCRIPTN NARRATIVE                      DATE      ENTRY DATE    CASH VALUE   De 
    7 -------------------------------------------------------------------------------------------------------------------------------------------- 
    8WTXMF3131131RIO TINTOAUDMLWSINBFADJUSTMEN2385.26 ML W/S INT BD FD   29-Dec-088-Jan-09             N
    9WTXMF3131131RIO TINTOAUDAUDCUST REINVEST INTEREST RECEIVED           1-Jan-0927-Jan-09             N 
    10WTXMF3131131RIO TINTOAUDAUDCUST REINVEST INTEREST RECEIVED           1-Jan-0927-Jan-09-572.4N 
    11WTXMF3131131RIO TINTOAUDAUDCUST REINVEST INTEREST RECEIVED           1-Feb-0923-Feb-09             N 
    12WTXMF3131131RIO TINTOAUDAUDCUST REINVEST INTEREST RECEIVED           1-Feb-0923-Feb-09-519.62N 
    13WTXMF3131131RIO TINTOAUDAUDCUST REINVEST INTEREST RECEIVED           1-Feb-0924-Feb-09             N 
    14WTXMF3131131RIO TINTOAUDAUDCUST REINVEST INTEREST RECEIVED           1-Feb-0924-Feb-09519.62N 
    15WTXMF3131131RIO TINTOAUDAUDCUST REINVEST INTEREST RECEIVED           1-Feb-0924-Feb-09             N 
    16WTXMF3131131RIO TINTOAUDAUDCUST REINVEST INTEREST RECEIVED           1-Feb-0924-Feb-09-689.22N 
    17WTXMF3878787RIO TINTOAUDMLWSINBFSALE     490001.26 ML W/S INT BD FD 16-Feb-0917-Feb-09-480838.24N
    18WTXMF3131131RIO TINTOAUDAUDCUST REINVEST INTEREST RECEIVED           1-Mar-0923-Mar-09             N 
    19WTXMF3131131RIO TINTOAUDAUDCUST REINVEST INTEREST RECEIVED           1-Mar-0923-Mar-09-312.37N 
    20WTXMF34544RIO TINTOAUDMLWSINBFSALE     258414.13 ML W/S INT BD FD 16-Mar-0917-Mar-09-251307.75N
    21WTXMF3131131RIO TINTOAUDMLWSINBFSALE     273715.73 ML W/S INT BD FD 23-Mar-0925-Mar-09-267475.02N
    22WTXMF3131131RIO TINTOAUDMLWSINBFSALE     401875.41 ML W/S INT BD FD 27-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 


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

  4. #4
    Board Regular
    Join Date
    Jan 2006
    Location
    Kenosha, WI
    Posts
    207

    Default Re: VBA to shift cells to the left

    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?
    I solemnly do swear, from this point forward, that I will not use sumproduct in place of pivot tables.

  5. #5
    Board Regular
    Join Date
    Jan 2006
    Location
    Kenosha, WI
    Posts
    207

    Default Re: VBA to shift cells to the left

    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.
    I solemnly do swear, from this point forward, that I will not use sumproduct in place of pivot tables.

  6. #6
    Board Regular mooseman's Avatar
    Join Date
    Jul 2004
    Location
    Pittsburgh, PA
    Posts
    179

    Default Re: VBA to shift cells to the left

    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

  7. #7
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    11,201

    Default Re: VBA to shift cells to the left

    Katy Jordan,

    Before the macro:


    Sheet1

     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 


    Excel tables to the web >> Excel Jeanie HTML 4


    After the macro:


    Sheet1

     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 


    Excel tables to the web >> Excel Jeanie HTML 4



    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.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  8. #8
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    11,201

    Default Re: VBA to shift cells to the left

    Katy Jordan,

    You should not see the * characters in your worksheet.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  9. #9
    Board Regular
    Join Date
    Jun 2008
    Location
    Oslo
    Posts
    596

    Default Re: VBA to shift cells to the left

    Hi Hiker

    the code did not work on this line

    WTXMF312121RIO TINTOAUDINT0005 SALE 39727.08 INTECH BAL GR FD C29-Dec-082-Jan-09-27690.6N


    Quote Originally Posted by hiker95 View Post
    Katy Jordan,

    Before the macro:


    Sheet1

    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


    Excel tables to the web >> Excel Jeanie HTML 4


    After the macro:


    Sheet1

    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


    Excel tables to the web >> Excel Jeanie HTML 4



    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.

  10. #10
    Board Regular
    Join Date
    Jun 2008
    Location
    Oslo
    Posts
    596

    Default Re: VBA to shift cells to the left

    Mooseman, some of the cells are shifted to Col M, i changed n to m but it didnt work

    Quote Originally Posted by mooseman View Post
    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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com