VBA select cells to the right

[Chenboy2]

Hi, i am trying to get the code that will select three cells across, including the active cell based on where the active cell is, insert new cells under the three cells, clearing the contents, but maintaining the formatting or just insert a new row, keeping the formatting. I don't have any other data on the rest of that row.

Like for example, I have some data already in columns A, B, and C and I'm in the active cell is A2 and I want to insert a new row or blank cells under A2, B2 and C2, but retain the formatting

Thanks!

 

[Fluff]

How about

    ActiveCell.Offset(1).EntireRow.Insert

 

[Chenboy2]

Thanks, what about a fill down on the cell? I want the VBA to be able to work on the column only.

 

[Fluff]

You originally said

insert new cells under the three cells, clearing the contents, but maintaining the formatting or just insert a new row, keeping the formatting.

The code I supplied inserts a new row under the active cell & maintains the formatting.
Is that not what you're after?

 

[Chenboy2]

I know, I was debating between whether to confine it to just the three columns and not the entire row. I forgot that I wanted to fill down the third column's cell. Sorry, it was around 3am when I posted this.

 

[Chenboy2]

I have a formula I want to fill down in the third column when I insert the new row. Originally I wanted to do a copy and insert cells below and then clear any data, at the same time let the formula fill down as well.

 

[Fluff]

Fair enough

Sub macro1()
     ActiveCell.Offset(1).EntireRow.Insert
     Range("C" & ActiveCell.Row).Resize(2).FillDown
End Sub
Sub Macro2()
     ActiveCell.Offset(1).Resize(, 3).Insert
     Range("C" & ActiveCell.Row).Resize(2).FillDown
End Sub

2 options, macro1 inserts a complete row, whilst macro2 inserts 3 cells