VBA to insert row / colum

Sub InsertMania()

'Insert row above active cell
ActiveCell.EntireRow.Insert

'Insert row below active cell
ActiveCell.Offset(1).EntireRow.Insert

'Insert column to the left of the active cell
ActiveCell.EntireColumn.Insert

'Insert column to the right of the active cell
ActiveCell.EntireColumn.Offset(0, 1).Insert

End Sub

way hey..........

i owe you one.........

+ do you know how i could have the new column / row have the same formulas and conditional formating as the active cell? - maybe a simple copy paste of original row / column.

:wink:

Try recording a macro regarding the copy and paste for row-to-row, and column-to-column. Plug the relevant recorded code into each scenario I posted for you. Post back if you get stuck, with specifics about what you tried to do and what did not work, so someone can assist if need be.

no problem, will give it a go.............

Hi,

could you explain this with a excel sheet attached please ..

Tom Urtis said:

Sub InsertMania()

'Insert row above active cell
ActiveCell.EntireRow.Insert

'Insert row below active cell
ActiveCell.Offset(1).EntireRow.Insert

'Insert column to the left of the active cell
ActiveCell.EntireColumn.Insert

'Insert column to the right of the active cell
ActiveCell.EntireColumn.Offset(0, 1).Insert

End Sub

Click to expand...

I have a question: how do you VBA to automatically insert a row on every 96 rows? I have a large database that requires a row be inserted on every 96 rows. Can someone help with the VBA?

mrattana said:

I have a question: how do you VBA to automatically insert a row on every 96 rows? I have a large database that requires a row be inserted on every 96 rows. Can someone help with the VBA?

Click to expand...

Function AddRowEvery96()
End Function

Hi guys,

I'm a total VBA novice (just 2 days old!) and I'm looking to utilise aspects of the above posts, but I'm lost!

I currently have a macro in a worksheet (which works perfctly, but I guess that it could be trimmed down?) and want to expand on it.

The existing macro automatically unprotects the sheet, hides/unhides various columns and then protects the sheet again. The macro is as follows ...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect
If Range("E2").Value = 0 Then
Columns("G:H").EntireColumn.Hidden = True
Else
Columns("G:H").EntireColumn.Hidden = False
End If
If Range("E1").Value = 0 Then
Columns("B").EntireColumn.Hidden = True
Else
Columns("B").EntireColumn.Hidden = False
End If
If Range("I4").Value = 0 Then
Columns("I").EntireColumn.Hidden = True
Else
Columns("I").EntireColumn.Hidden = False
End If
If Range("J4").Value = 0 Then
Columns("J").EntireColumn.Hidden = True
Else
Columns("J").EntireColumn.Hidden = False
End If
If Range("K4").Value = 0 Then
Columns("K").EntireColumn.Hidden = True
Else
Columns("K").EntireColumn.Hidden = False
End If
If Range("L4").Value = 0 Then
Columns("L").EntireColumn.Hidden = True
Else
Columns("L").EntireColumn.Hidden = False
End If
If Range("M4").Value = 0 Then
Columns("M").EntireColumn.Hidden = True
Else
Columns("M").EntireColumn.Hidden = False
End If
If Range("N4").Value = 0 Then
Columns("N").EntireColumn.Hidden = True
Else
Columns("N").EntireColumn.Hidden = False
End If
If Range("O4").Value = 0 Then
Columns("O").EntireColumn.Hidden = True
Else
Columns("O").EntireColumn.Hidden = False
End If
If Range("P4").Value = 0 Then
Columns("P").EntireColumn.Hidden = True
Else
Columns("P").EntireColumn.Hidden = False
End If
If Range("Q4").Value = 0 Then
Columns("Q").EntireColumn.Hidden = True
Else
Columns("Q").EntireColumn.Hidden = False
End If
If Range("R4").Value = 0 Then
Columns("R").EntireColumn.Hidden = True
Else
Columns("R").EntireColumn.Hidden = False
End If
ActiveSheet.Protect
End Sub

What I now want to do is to copy the row below a given cell if a value (text) is found in that cell and insert (paste) the copied row (containing formulas) below itself.

My starting reference would be if cell F5 contains text, then copy row 6 and insert it below row 6, else do nothing. From there the macro would need to look at cell F6 and if it contains text, then it would copy row 7 and insert it below row 7. After each copy/paste operation the macro would move down to the next cell/row automatically.

If I could get this to work without having to add any buttons (like the code shown above), it would be perfect!

* Bump *

Apologies for the bump, but I'm getting nowhere fast! Any help would be greatly appreciated.