Macro needed for a new row and a new column.

**charles.2345** · 11-06-2008, 01:53 AM

Hello experts,

I was wondering if we can program a macro to:
1) Insert a row above the current cell selected, and then filling a single space in the first 24 cells of the row.
2) Insert a column above the current cell selected, and then filling a single space in the first 100 cells of the column.

If you give me the code for 1 and 2, I will probably be able to make the third macro which does both of the above.

Thanks.
Charles.

**StephenR** · 11-06-2008, 02:17 AM

This is a little confusing.

filling a single space in the first 24 cells of the row

What does this mean? Filling with what?

Insert a column above the current cell selected

Columns can't be above - do you mean to the left or right?

**Paul** · 11-06-2008, 02:28 AM

Not sure if this is what you're after, but making some assumptions it might work. This code inserts a row above the current cell then inserts a space character into columns A:X of that new row. It then inserts a column to the left of the active cell and inserts a space character in the first 100 cells in that column.

Sub insertRC()
    ActiveCell.EntireRow.Insert
    Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 24)).Value = " "
    ActiveCell.EntireColumn.Insert
    Range(Cells(1, ActiveCell.Column), Cells(100, ActiveCell.Column)).Value = " "
End Sub

If that's not what you're looking for you will need to provide more details.

**charles.2345** · 11-06-2008, 04:07 PM

Thanks PjoAcquin. It worked. But i want to ask this:

Why does running this macro twice give error?

 
Sub fill_final()
'
' fill_final Macro
' Macro recorded 11/3/2008 by ***.
'
' Keyboard Shortcut: Ctrl+Shift+E
'
Range("A1:V100").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
' error in above line
Selection.FormulaR1C1 = " "
End Sub

Is it because there is no blank cell to select?
How can I modify the code so that I don't get an error if I repeat run the macro.

Best,
Charles.