Using VBA to Insert Rows in Excel When Row Value Changes 5

[mlunter]

I am working with a table like the following:

ROW 1
A
A
A
B
B
C
C
C

When the row value changes from A to B, and B to C, I would simply like insert a row. My dataset is huge, so doing this manually is not an option. I looked through the forums for help but was unable to find anything that resembled my problem. I am relatively new to VBA, and so helping me out with this problem will really help me with VBA syntax so that I won't have to ask such basic questions next time.

Thank you for your help,
MSL

 

[Bong]

I'm a novice, myself, but I think I can help you.
If I understand your setup, you're working your way down the rows looking for a change of the value in column A (cell Ai, where i equals the row number). The first question is, do you know how may rows there are or do you need to determine that in your code? Let's assume you don't know. Here's a snippet I learned in another thread to determine the last row of data:

Set lastRow = Range("a65536").End(xlUp).row


So now you want to loop through the rows looking at the value of the first cell in each. When that value changes, you want to insert a row. I don't know the syntax for that offhand so I turned on the macro recorder in Excel and inserted a row. Here's what I got:

Rows("14:14").Select
Selection.Insert Shift:=xlDown


So, I suggest something like this:

    Set lastRow = Range("a65536").End(xlUp).row
    set rval=cells(1,1).value
    for i=1 to lastRow
      if cells(i,1).value<>rval then
        rval=cells(i,1).value
        activecell.entirerow.insert shift:=xlDown
      end if
    next

alternatively, you don't have to know how many rows there are:

    For Each rw In activesheet.Rows
      i=rw.row
      if cells(i,1).value<>rval then
        rval=cells(i,1).value
        activecell.entirerow.insert shift:=xlDown
      end if
    next

_________________
Bob Rashkin

 

[Gavona]

I wonder how the speed of that solution would compare with using data, subtotals on your data, displaying just the rows with subtotals, clearing these cells and then removing the outline. Something like

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
Selection.CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).ClearContents
Cells.ClearOutline

Gavin

 

[mlunter]

Bob,

Thank you for the reply! The second set of code is almost there. What I believe it does is count the number of times the value in Row 1 changes and insert that many rows into Row 1 at the top of the worksheet. Any suggestions on how to get it to insert the rows between value changes (e.g. between AAAA and BB and CCCCCCC and DDD)?

The first set of code did not do much.

Thanks again,
MSL

 

[mlunter]

Gavin,

Well done. That solution works perfectly.

Thank you both for the help!!
Mark S. Lunter II

 

[PHV]

Another way:
Dim rval As String, i As Long
rval = Cells(1, 1): i = 2
Do Until Trim(Cells(i, 1) & "") = ""
If Cells(i, 1) <> rval Then
rval = Cells(i, 1)
Rows(i).Insert shift:=xlDown
i = i + 1
End If
i = i + 1
Loop

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Bong]

Gavin's solution is clearly the better one, but just for completeness...
I forgot to select the cell before the insert:

    For Each rw In activesheet.Rows
      i=rw.row
      if cells(i,1).value<>rval then
        rval=cells(i,1).value
        [b]cells(i,1).select[/b]
        activecell.entirerow.insert shift:=xlDown
      end if
    next

_________________
Bob Rashkin

 

[Gavona]

Thanks for the star. I would genuinely like to know how the speeds compare if you decide to implement more than one of the solutions.


Gavin

 

[lfitzhugh]

Well, if you could stand to tell me how to enter or call this code, I would really appreciate it. I'm sorry I'm such a novice but doing this successfully will jump start my learning curve. Personally I don't care how long it takes, but I suppose I'd use Gavonna's suggestion as it seems to be most highly thought of. Any replies appreciated. Laurel

 

[Gavona]

As a novice start by doing what I described without using code but getting the recorder to create the code for you.
Tools, Macro, Record new macro......

select your entire data range. Either of the following should do this although the second requires you not to have totally blank rows and columns within the data.
(i)select the top left cell. Hold Ctrl and Shift down and press END
or (ii)select a cell in your data range then Edit,Goto, Special, CurrentRegion

Stop the Macro recorder.
Tools, Macro, macros...Select the macro and choose edit.
Copy my code beneath yours but before the "end sub" line




Gavin

 

[lfitzhugh]

Worked like a charm, Gavona! Thank you!

 

[lfitzhugh]

How could I edit this macro to use the 4th column instead of the 1st column? (To insert a blank line when data in the 4th column is different from the preceeding data.) TIA.

 

[PHV]

Replace this:
GroupBy:=1,
with this:
GroupBy:=4,

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886