+ Reply to Thread
Results 1 to 23 of 23

macro to cut data from a row starting from column p and paste it into the row below at d

  1. #1
    Registered User
    Join Date
    03-21-2017
    Location
    Germany
    MS-Off Ver
    MS OFFICE 2007, 2010, 2013
    Posts
    3

    macro to cut data from a row starting from column p and paste it into the row below at d

    macro to cut data from a row starting from column p and paste it into the row below at d if it is empty

    Dear Guys I have a list with names of different cities around the world for over 3000 city.

    I need a VBA macro code to be able to cut and paste as shown in the attached file.

    note: in the attached file sheet1 is (before) sheet2 is (after)


    thanks for your help.



    macro to cut data from a row starting from column p and paste it into the row below at d if it is empty

    I usually use MS OFFICE 2010, 2013. But for now I am using office 2007
    Attached Files Attached Files
    Last edited by Abbes; 04-06-2017 at 07:25 PM. Reason: using office 2007

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Cool Hi ! Try this !


    Paste next demonstration to worksheet module :

    PHP Code: 
    Sub Demo()
         Const 
    """"
           
    Dim Rg As RangeR&
           
    Application.ScreenUpdating False
        
    Do Until Cells(11).Value ""
                
    1
           Set Rg 
    Cells(RColumns.Count).End(xlToLeft)
            If 
    Rg.Column 16 Then
                Range
    (Cells(R16), Rg).Cut Cells(14)
                
    Cells(R16).Value C
            
    ElseIf Rg.Value <> C Then
                   Rg
    (12).Value C
            End 
    If
        
    Loop
           Set Rg 
    Nothing
           Application
    .ScreenUpdating True
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon Add Reputation !

  3. #3
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Hi ! Try this !

    Maybe it's just my Excel, but to copy the code and paste into the module doesn't paste right. I would guess because its in PHP Code Tags.

    Quote Originally Posted by Marc L View Post

    Paste next demonstration to worksheet module :

    PHP Code: 
    Sub Demo()
         Const 
    """"
           
    Dim Rg As RangeR&
           
    Application.ScreenUpdating False
        
    Do Until Cells(11).Value ""
                
    1
           Set Rg 
    Cells(RColumns.Count).End(xlToLeft)
            If 
    Rg.Column 16 Then
                Range
    (Cells(R16), Rg).Cut Cells(14)
                
    Cells(R16).Value C
            
    ElseIf Rg.Value <> C Then
                   Rg
    (12).Value C
            End 
    If
        
    Loop
           Set Rg 
    Nothing
           Application
    .ScreenUpdating True
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon Add Reputation !
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Hi ! Try this !

    Quote Originally Posted by Marc L View Post

    Paste next demonstration to worksheet module :

    PHP Code: 
    Sub Demo()
         Const 
    """"
           
    Dim Rg As RangeR&
           
    Application.ScreenUpdating False
        
    Do Until Cells(11).Value ""
                
    1
           Set Rg 
    Cells(RColumns.Count).End(xlToLeft)
            If 
    Rg.Column 16 Then
                Range
    (Cells(R16), Rg).Cut Cells(14)
                
    Cells(R16).Value C
            
    ElseIf Rg.Value <> C Then
                   Rg
    (12).Value C
            End 
    If
        
    Loop
           Set Rg 
    Nothing
           Application
    .ScreenUpdating True
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon Add Reputation !
    I had to put into the normal "
    Please Login or Register  to view this content.
    " Tags

    Please Login or Register  to view this content.

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Re: macro to cut data from a row starting from column p and paste it into the row below at


    No issue on my side with two different Excel and Windows versions …

    Both with Firefox …

  6. #6
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: macro to cut data from a row starting from column p and paste it into the row below at

    @Marc L

    I'm using Windows 10 with Excel 2016. If I copy the code within the PHP code tags, its acting weird in the module and highlighting linesred. I back it all out and still can not get it to compile. Strange...
    Attached Images Attached Images

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Re: macro to cut data from a row starting from column p and paste it into the row below at


    Which webbrowser ? Internet Explorer ?!

  8. #8
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: macro to cut data from a row starting from column p and paste it into the row below at

    Yes Explorer

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Exclamation Specific to IE !


    As since a couple of days there are some issues between this site and Internet Explorer,
    see this thread and another one

  10. #10
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: macro to cut data from a row starting from column p and paste it into the row below at

    Ah I see, thanks my friend.

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Re: macro to cut data from a row starting from column p and paste it into the row below at


    Thanks for your warning as now I maybe understand why I've no answer after my codes !

  12. #12
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: macro to cut data from a row starting from column p and paste it into the row below at

    No problem, Your Code in this thread does work for me. Just not able to copy the Codes inside PHP code tags using IE and paste correctly into excel.

  13. #13
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Re: macro to cut data from a row starting from column p and paste it into the row below at


    Maybe by pasting to Notepad then with a new copy from Notepad …

    PHP code tags are usefull to see code even if not logged to forum.

  14. #14
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: macro to cut data from a row starting from column p and paste it into the row below at

    Nope, just tried that. The only way I can get it to work is to reply to your post by "Reply With Quote" and then copy the plain code txt from inside the PHP code tags

  15. #15
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Re: macro to cut data from a row starting from column p and paste it into the row below at


    So weird …

  16. #16
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: macro to cut data from a row starting from column p and paste it into the row below at

    I was thinking maybe the[Indent][Indent] tags had something to do with it but nope lol
    Last edited by mike7952; 04-07-2017 at 05:35 AM.

  17. #17
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Question


    Do you have same issue with this thread or with that another one ?

  18. #18
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: macro to cut data from a row starting from column p and paste it into the row below at

    Yes sir, Both of those threads I can not copy and paste into the module and get the code to compile with IE Windows 10
    Last edited by mike7952; 04-07-2017 at 05:55 AM.

  19. #19
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Re: macro to cut data from a row starting from column p and paste it into the row below at


    Ok, thanks !

  20. #20
    Registered User
    Join Date
    03-21-2017
    Location
    Germany
    MS-Off Ver
    MS OFFICE 2007, 2010, 2013
    Posts
    3

    Re: Hi ! Try this !

    Dear Marc the Code diid work but just for the first row it didn't contiunue till the end of the sheet.

    If I am not clear with what I wrote hopefully the example sheet can explain what I want.

    Many thanks in advance.

  21. #21
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Re: macro to cut data from a row starting from column p and paste it into the row below at


    Try my code with your attachment, as it works on my side …

  22. #22
    Registered User
    Join Date
    03-21-2017
    Location
    Germany
    MS-Off Ver
    MS OFFICE 2007, 2010, 2013
    Posts
    3

    Re: macro to cut data from a row starting from column p and paste it into the row below at

    Thanks Marc I think i missed some thing while copying the code it works perfectly.

    May i ask you to write me some eplanations to the code to understand how it works so I can improve my knowledege.

    Many thanks.

  23. #23
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,124

    Re: macro to cut data from a row starting from column p and paste it into the row below at


    If last data cell of a row is after column P then range from column P until last cell is moved a row under …

    Place text cursor on a statement, hit F1 key and read : all is in VBA inner help !

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Code to copy and paste a data set into a different worksheet starting last row
    By hawk77EF in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2015, 11:13 AM
  2. [SOLVED] paste data on the clipboard starting at a specific cell
    By djarum11 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-17-2015, 12:34 PM
  3. Paste range into next available row starting at column b
    By Burt_100 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-04-2015, 10:02 AM
  4. Macro that will print each column starting with column B on a seperate page
    By awebzz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-18-2013, 12:25 PM
  5. Macro to search for text then copy data from that row starting in different column
    By balston2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-09-2013, 10:43 AM
  6. Macro to copy and paste special values for column data and filter column data
    By ascottbag in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-10-2012, 02:15 PM
  7. Change Paste Starting Cell In Macro
    By nebb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-27-2006, 05:52 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1