Macro to insert blank row after data value change for multiple rows

[Indians1022]

Hello,

I am having some trouble with figuring out how to insert blank rows to my data set. I have been able to find information and figure out how to insert blank rows based off of one column. The problem for me is that I need to be able to do this for multiple columns where the data value changes. I've tried several different code combinations, but I have not been successful with combining them into one single macro yet. I have shared a sample of my data and the result that I am trying to achieve.

Data Set: Data Set starts in cell "A2"

[TABLE="width: 774"]
<tbody>[TR]
[TD]AUTOMOTIVE[/TD]
[TD] GENERAL AUTOMOTIVE[/TD]
[TD] TUNE UP EQUIPMENT[/TD]
[TD]   TESTERS & METERS[/TD]
[/TR]
[TR]
[TD]AUTOMOTIVE[/TD]
[TD] GENERAL AUTOMOTIVE[/TD]
[TD] WHEEL & JACKEQUIPMENT[/TD]
[TD]   AIR PUMPS[/TD]
[/TR]
[TR]
[TD]AUTOMOTIVE[/TD]
[TD] GENERAL AUTOMOTIVE[/TD]
[TD] WHEEL & JACKEQUIPMENT[/TD]
[TD]   JACKS[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 774"]
<tbody>[TR]
[TD]AUTOMOTIVE[/TD]
[TD] GENERAL AUTOMOTIVE[/TD]
[TD] WIPER BLADES[/TD]
[TD]   WIPER BLADES SETS[/TD]
[/TR]
[TR]
[TD]AUTOMOTIVE[/TD]
[TD] RV & MOBILEHOME SUPPLIES[/TD]
[TD] MOBILE HOME SUPPLIES[/TD]
[TD]   MOBILE HOME ELECTRICAL[/TD]
[/TR]
</tbody>[/TABLE]

Result Needed:

AUTOMOTIVE
AUTOMOTIVE    GENERAL AUTOMOTIVE
AUTOMOTIVE    GENERAL AUTOMOTIVE            TUNE UP EQUIPMENT
AUTOMOTIVE    GENERAL AUTOMOTIVE            TUNE UP EQUIPMENT          TESTERS & METERS
AUTOMOTIVE    GENERAL AUTOMOTIVE            WHEEL & JACK EQUIP.    
AUTOMOTIVE    GENERAL AUTOMOTIVE            WHEEL & JACK EQUIP.         AIR PUMPS
AUTOMOTIVE    GENERAL AUTOMOTIVE            WHEEL & JACK EQUIP.         JACKS
AUTOMOTIVE    GENERAL AUTOMOTIVE            WIPER BLADES
AUTOMOTIVE    GENERAL AUTOMOTIVE            WIPER BLADES                   WIPER BLADE SETS
AUTOMOTIVE
AUTOMOTIVE    RV & MOBILEHOME SUPPLIES
AUTOMOTIVE    RV & MOBILEHOME SUPPLIES    MOBILE HOME SUPPLIES   
AUTOMOTIVE    RV & MOBILEHOME SUPPLIES    MOBILE HOME SUPPLIES    MOBILE HOME ELECTRICAL
AUTOMOTIVE    RV & MOBILEHOME SUPPLIES    MOBILE HOME SUPPLIES    MOBILE HOME HARDWARE

So far my code looks like this:

Sub AddRows()


Dim aRow As Integer
Dim aCol As Integer
Dim aRange As Range


Set aRange = Range("A1")


aRow = aRange.Row
aCol = aRange.Column


Do


If Cells(aRow + 1, aCol) <> Cells(aRow, aCol) Then
    Cells(aRow + 1, aCol).EntireRow.Resize(3).Insert shift:=xlDown
    Cells(aRow + 1, aCol) = Cells(aRow + 4, aCol)
    Cells(aRow + 2, aCol) = Cells(aRow + 4, aCol)
    Cells(aRow + 3, aCol) = Cells(aRow + 4, aCol)
    Cells(aRow + 4, aCol) = Cells(aRow + 4, aCol)
    Cells(aRow + 2, 2) = Cells(aRow + 4, 2)
    Cells(aRow + 3, 2) = Cells(aRow + 4, 2)
    Cells(aRow + 3, 3) = Cells(aRow + 4, 3)
    aRow = aRow + 5
Else
    aRow = aRow + 1
End If


Loop While Not Cells(aRow, aCol).Text = ""


End Sub

I am still pretty new to VBA, so this is all still a learning process for me. With my code that I shared above I have been able to add rows based off of Column A and put the correct values in the newly inserted rows, but then I have been running into trouble with trying to do this to Columns B & C.

I have a feeling that I am coding the macro wrong due to my lack of experience with VBA and that it probably isn't as difficult as I am making it seem. If anyone has any pointers or ideas to help nudge me in the right direction, I would greatly appreciate it. Thank you in advance!

Have a wonderful day, God Bless!
- Indians1022

 

[jolivanes]

If I understand your request right, you want to insert an empty cell at value change. You want this for each column.

Sub Maybe()
Dim i As Long, ii As Long
Application.ScreenUpdating = False
    For i = 1 To Cells.Find("*", , , , xlByColumns, xlPrevious).Column
        For ii = Cells(Rows.Count, i).End(xlUp).Row To 2 Step -1
            If Cells(ii, i).Value <> Cells(ii - 1, i).Value Then Cells(ii, i).Insert Shift:=xlDown
        Next ii
    Next i
Application.ScreenUpdating = True
End Sub

 

[Indians1022]

Thank you jolivanes! This works perfect for inserting empty cells at value changes. Do you know what would be the best way to insert an entire row and then, since this code runs from the bottom up, fill the cell from the cell value below to the new cell added? I need to somehow create a row stagger for each new subcategory. Also is there a way to only run this on the first three columns? I do not need to add cells to the cell value change in column 4. Thanks again for your time and help.

 

[jolivanes]

Re: Inserting row.
When you start inserting rows in column 2, you'll create empty cells in column 1 and when doing the same in column 3, you'll create empty cells in columns 1 and 2
Is that what you need?

Sub Maybe_B()
Dim i As Long, ii As Long
Application.ScreenUpdating = False
    For i = 1 To 3
        For ii = Cells(Rows.Count, i).End(xlUp).Row To 2 Step -1
            If Cells(ii, i).Value <> Cells(ii - 1, i).Value Then Cells(ii, i).EntireRow.Insert: Cells(ii, i).Value = Cells(ii, i).Offset(1).Value
        Next ii
    Next i
Application.ScreenUpdating = True
End Sub

 

[Indians1022]

Works perfect! Thank you again jolivanes. You were a huge help. Have a great rest of your day.