+ Reply to Thread
Results 1 to 4 of 4

Insert row if the value of a column changes

Hybrid View

  1. #1
    Registered User
    Join Date
    06-19-2013
    Location
    brazil
    MS-Off Ver
    Excel 2007
    Posts
    64

    Insert row if the value of a column changes

    Please, I need to insert a row in a sheet if the value in column F changes, like attached files.

    The best code that I could adapt was found here:
    http://www.mrexcel.com/forum/excel-q...lumns-c-d.html

    Then I changed it to:

    Sub InsertRows()
    
        Dim myLastRow As Long
        Dim myRow As Long
        
        Application.ScreenUpdating = False
        
    '   Find last row with data in column A
        myLastRow = Cells(Rows.Count, "A").End(xlUp).row
        
    '   Loop through all rows in column A, starting at row 1
        For myRow = 1 To myLastRow
    '       Check to see if value in column A is 0
            If Len(Cells(myRow, "F")) = 1 And Cells(myRow, "F") = 2 Then
    '           Insert blank rows from A to F
                Range(Cells(myRow, "A"), Cells(myRow, "F")).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                End If
        Next myRow
        
        Application.ScreenUpdating = True
        
    End Sub
    But it doesn't work.
    Attached Files Attached Files
    Last edited by dualaudio454252; 04-19-2014 at 02:36 PM. Reason: the code wasn 't wrapped

  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: Insert row if the value of a column changes

    Try:
    Sub InsertRows()
        Application.ScreenUpdating = False
        Dim LastRow As Long
        LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Dim x As Long
        On Error Resume Next
        For x = LastRow To 1 Step -1
            If Cells(x, "F") <> Cells(x - 1, "F") Then
                Rows(x).EntireRow.Insert
            End If
        Next x
        Rows(1).EntireRow.Delete
        Application.ScreenUpdating = True
    End Sub
    Last edited by Mumps1; 04-19-2014 at 02:57 PM.
    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
    Registered User
    Join Date
    06-19-2013
    Location
    brazil
    MS-Off Ver
    Excel 2007
    Posts
    64

    Re: Insert row if the value of a column changes

    That's it, Mumps1 !!!!!!!!
    All the best for you!

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

    Re: Insert row if the value of a column changes

    My pleasure.

+ 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. Find most recent date in Column Headings in a Range, insert new column and heading
    By mikey3580 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-07-2014, 08:28 AM
  2. [SOLVED] Insert Row & Insert Column Macros
    By amfrancis in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-19-2014, 01:07 PM
  3. Insert column and fill column upto where data is in previous column
    By aka189 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-24-2012, 06:07 AM
  4. Replies: 1
    Last Post: 08-24-2012, 03:18 PM
  5. Insert Column then insert designated number
    By houseguy007 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 03-18-2010, 08:15 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