+ Reply to Thread
Results 1 to 5 of 5

Vba that moves cells with text, (up and to left, insert row/ insert copied cell)

Hybrid View

  1. #1
    Registered User
    Join Date
    08-30-2018
    Location
    North Bergen, NJ
    MS-Off Ver
    Office 2013
    Posts
    23

    Vba that moves cells with text, (up and to left, insert row/ insert copied cell)

    So I have a formula which is testing some key stuff (unimportant right now) if true then the cells get text, if not then blank. I copy and paste values so it is just blank or text. What I need is if the cells contain text then cut and paste the data above the cells to their immediate left. essentially inserting a new row and moving cells below down. I've included a sample excel of what I need it to do, if anyone can help it would be much appreciated. Thank You! I'm hoping the excel attached, but if it didn't please let me know cause I'm not seeing it even though it says it did.

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,026

    Re: Vba that moves cells with text, (up and to left, insert row/ insert copied cell)

    Try:
    Sub MoveCells()
        Application.ScreenUpdating = False
        Dim LastRow As Long
        LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Dim rng As Range
        For Each rng In Range("D1:D" & LastRow)
            If rng <> "" Then
                Rows(rng.Row).Insert
                rng.Resize(1, 3).Cut Cells(rng.Row - 1, 1)
            End If
        Next rng
        Application.ScreenUpdating = True
    End Sub
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Contributor
    Join Date
    09-15-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    135

    Re: Vba that moves cells with text, (up and to left, insert row/ insert copied cell)

    Hi Mumps1,

    Awesome the code is working perfectly, am the new learner of VBA can you please explain the above said code how it's working particularly why we are using here for the "Resize". Please explain indeed.

    Regards
    Raju

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Vba that moves cells with text, (up and to left, insert row/ insert copied cell)

    A little less movement by loop backwards.
    Sub test()
        Dim i As Long
        ' Loop commence from bottom to the top
        For i = Range("d" & Rows.Count).End(xlUp).Row To 1 Step -1
            If Cells(i, "d").Value <> "" Then
                Cells(i, "d").Resize(, 3).Cut  '<- Cut(Ctrl + x) d:f
                Cells(i, 1).Resize(, 3).Insert xlShiftDown  '<- Insert cutted data to the same row in a:c
            End If
        Next
    End Sub

  5. #5
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,026

    Re: Vba that moves cells with text, (up and to left, insert row/ insert copied cell)

    Here is the macro with some explanatory comments. I hope this helps.
    Sub MoveCells()
        Application.ScreenUpdating = False 'turns screen refreshing off to prevent screen flickering and speed up the macro
        Dim LastRow As Long
        LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'finds last row with data
        Dim rng As Range
        For Each rng In Range("D1:D" & LastRow) 'loops through all the cells in column D
            If rng <> "" Then 'checks to make sure the cell is not blank
                Rows(rng.Row).Insert 'inserts blank row above
                rng.Resize(1, 3).Cut Cells(rng.Row - 1, 1) 'resizes the range to include columns D, E and F and then cuts it to the blank row above
            End If
        Next rng
        Application.ScreenUpdating = True 'turns screen refreshing back on
    End Sub

+ 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. [SOLVED] Macro to insert rows with copied text a certain amount of times based on a value in a cell
    By rhendrickson11 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-21-2017, 11:20 AM
  2. [SOLVED] Select matching cell and insert value 4 cells to the left of selected cell?
    By TomToms in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-03-2014, 11:46 AM
  3. Replies: 3
    Last Post: 10-25-2012, 07:10 AM
  4. [SOLVED] Insert Copied Cells multiple times, shift cell down
    By system in forum Excel General
    Replies: 18
    Last Post: 09-21-2012, 12:47 AM
  5. Insert Copied Cells multiple times, shift cell down
    By system in forum Excel General
    Replies: 1
    Last Post: 09-08-2012, 10:49 PM
  6. [SOLVED] Insert Copied Cells
    By Cyclewench in forum Excel General
    Replies: 2
    Last Post: 04-16-2012, 04:07 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