Insert rows in sheet with specific format and data

**ninikaki** · 10-19-2015, 09:46 AM

Good Afternoon,

First of all I want to clarify that these days I make my first steps in VBA...

I have an excel file with ~2500 rows.
Some times in the month, I have to input data in this sheet, after certain headers.
Also, I have to put blank lines for the headers that I do not have data.

For example, in the picture below, you can see the entries of OCT 19

sample.png

What I want to do, is create a macro which will insert every time a new line

sample2.png

before line with headers (header 2a, header 2b etc)

I tried to create a macro, but with no success....

Sub INSERT_ROWS()
Dim i As Long, y As Long
For i = Range("c" & Rows.Count).End(3).Row To 2 Step -1
If Cells(i, "c").Offset(y) = "HEADER 1" Then
Rows(i).Insert
End If
Next i
End Sub

Could, someone help me pls?..

**vichopalacios** · 10-20-2015, 02:21 PM

Please check the notes in the code

Sub INSERT_ROWS()

Dim i As Long, y As Long
'you need to asign a value to the variable "y"
'If you put y = 0, the new row is inserted just over the "HEADER 1"
'If you put y = -1, the new row is inserted just under the "HEADER 1"
'If you put y = -2 the new row is inserted just under the "HEADER 2b"

y = 1
For i = Range("c" & Rows.Count).End(3).Row To 2 Step -1
        Cells(i, "c").Offset(y).Select
'this need to assure that "HEADER 1" is in column "C"
    If Cells(i, "c").Offset(y) = "HEADER 1" Then
        Rows(i).Insert
    End If
Next i

End Sub

Insert rows in sheet with specific format and data

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Insert rows in sheet with specific format and data

Re: Insert rows in sheet with specific format and data

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro to insert specific number of rows, populate rows with data above except date

[SOLVED] Searching Values that Follow a Specific Format and Copy Rows to a New Sheet

Insert columns of data from sheet A into sheet B, WHERE a specific column/value is equal

HALP! Count and Insert Rows and Specific Data on Another Sheet.

From pivot table copy rows containing specific data to specific sheet

Copy and insert rows with contents and format based on specific number

Insert multiple rows in specific sections of sheet?

Bookmarks

Bookmarks

Posting Permissions