Solved Macro needed to Insert Rows Based on Cell Value Changes

December 10, 2015 at 19:27:23
Specs: Windows 8
I need a macro that will Insert 3 rows under every change in value in column E. I've inserted a column after Round E and used the formula =if(E2<>E3, "Last","") to enter the value of Last next to the cell in column E just before it changes to the next value in column E. I did this thinking I could come up with the macro myself using this column F data but I'm now stumped! Looking forward to hearing back!

See More: Macro needed to Insert Rows Based on Cell Value Changes

Report •


✔ Best Answer
December 18, 2015 at 11:12:48
Hi,

Here is very quick solution, it works, but there is one issue, when you run it the second time it again inserts 3 rows, this shouldn't be an issue to fix, I will try to fix it when I get sometime, as I said in my pm, I am now on Christmas holidays and the wife has a list (a big list) of chors for me to do :(

At least this is a start

Dim uRange
Dim lRange
Dim Bcell As Range
Dim FirstVal

Sub SortColumn()
    
    Columns("A:E").Sort key1:=Range("E1"), _
    order1:=xlAscending

    InsertRows
    
End Sub

Sub InsertRows()
    
    Set uRange = Range("E1")
    Set lRange = Range("E" & Rows.Count).End(xlUp)
    
    FirstVal = Range("E1").Value
    
    For Each Bcell In Range(uRange, lRange)
        
        If Bcell.Value <> FirstVal And Bcell.Value <> Empty Then
            
            For i = 1 To 3

                Bcell.EntireRow.Insert

            Next i

            FirstVal = Bcell
            
        End If
        
    Next Bcell
    
End Sub



#1
December 11, 2015 at 04:01:21
Can you clarify something please, so for example if you have the following in colume E

1 Hello
2 Hello
3 Hello
4 Mouse
5 Mouse
6 Tree
7 Tree

So you want to have 3 rows inserted after

3 Hello
5 Mouse

So something like

1 Hello
2 Hello
3 Hello
4
5
6
7 Mouse
8 Mouse
9
10
11
12 Tree
13 Tree

Is this correct?

message edited by AlwaysWillingToLearn


Report •

#2
December 12, 2015 at 13:18:51
Yes this is exactly correct!

Report •

#3
December 12, 2015 at 13:37:08
Ok shouldnt be too difficult with a vba macro, will have a look at it next week. There are other vba guys on here too so maybe they may come up with something sooner..

Report •

Related Solutions

#4
December 13, 2015 at 01:14:13
You legend, thank you so much for your help!

Report •

#5
December 18, 2015 at 11:12:48
✔ Best Answer
Hi,

Here is very quick solution, it works, but there is one issue, when you run it the second time it again inserts 3 rows, this shouldn't be an issue to fix, I will try to fix it when I get sometime, as I said in my pm, I am now on Christmas holidays and the wife has a list (a big list) of chors for me to do :(

At least this is a start

Dim uRange
Dim lRange
Dim Bcell As Range
Dim FirstVal

Sub SortColumn()
    
    Columns("A:E").Sort key1:=Range("E1"), _
    order1:=xlAscending

    InsertRows
    
End Sub

Sub InsertRows()
    
    Set uRange = Range("E1")
    Set lRange = Range("E" & Rows.Count).End(xlUp)
    
    FirstVal = Range("E1").Value
    
    For Each Bcell In Range(uRange, lRange)
        
        If Bcell.Value <> FirstVal And Bcell.Value <> Empty Then
            
            For i = 1 To 3

                Bcell.EntireRow.Insert

            Next i

            FirstVal = Bcell
            
        End If
        
    Next Bcell
    
End Sub


Report •

#6
January 21, 2016 at 17:34:52
This is absolutely perfect! Achieves exactly the result I was looking for! Thanks again mate, I really appreciate this!

Report •

#7
January 21, 2016 at 23:59:09
Hey, no worries at all, glad its working for you :)

Report •

Ask Question