insert rows based on text values in cells

**simarui** · 02-20-2014, 04:50 PM

Hello and thanks in advance,

I regularly run a report that comes out in a slightly different format each time depending on whether or not there is data for specific criteria. for instance (sample attached): criteria a, b, and c are in rows, 1, 2, and 3 in columns. a, b, and c are expected to repeat themselves several times. if there is no data in one instance of b then you may see something like abcacabc.

I'd like to loop through each row in column a, check to make sure that it is the correct value (either a, b, or c) and if not, insert a blank row so I can then copy the data to my final project without having to worry about formatting.

the example above would then become abca cabc

thanks again!

**xladept** · 02-20-2014, 06:34 PM

Hi Simarui,

Try this:

Sub ABC(): Dim r As Long, i As Long
r = 2: Do Until Cells(r, 1) = "" And Cells(r + 1, 1) = ""
ReComp:
If Trim(Cells(r, 1)) = "" Then
r = r + 1: GoTo ReComp: End If
If Trim(Cells(r, 1)) = "a" Then
NoComp:
    If Trim(Cells(r + 1, 1)) = "b" Then
HalfComp:
        If Cells(r + 2, 1) = "c" Then
            r = r + 3: GoTo FullComp
        Else: Cells(r + 2, 1).EntireRow.Insert
                r = r + 3: GoTo FullComp: End If
    Else: Cells(r + 1, 1).EntireRow.Insert     'the b row
    GoTo HalfComp: End If
Else: Cells(r, 1).EntireRow.Insert: GoTo NoComp: End If

FullComp: Loop
End Sub

**simarui** · 02-20-2014, 07:20 PM

perfection

thank you.

**xladept** · 02-20-2014, 07:54 PM

You're welcome! And, thanks for the rep!

insert rows based on text values in cells

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

insert rows based on text values in cells

Re: insert rows based on text values in cells

Re: insert rows based on text values in cells

Re: insert rows based on text values in cells

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Insert rows based on the current cell value and, then, copy some cells into the new rows

I need to insert rows using macros, based on rows values. Please Help!

Insert rows based on values in column

[SOLVED] Insert number of rows with data to new sheets based on values in cells

Insert rows based on start/end values

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions