VBA to insert row / colum

philhowell

Board Regular
Joined
Jun 11, 2002
Messages
175
Hi guy,

can't believe its been a year since i last posted on here.... (y)

i have been asked to write 4 seperate VBA modules;

1. insert a row above the cell selected
2. insert a row below the cell selected
3. insert a colum to the left of the cell selected
4. insert a colum to the right of the cell selected


any help is grrr8
Phil
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Upvote 0
way hey..........

i owe you one......... (y)

+ 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:
 
Upvote 0
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.
 
Upvote 0
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


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?
 
Upvote 0
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?

Function AddRowEvery96()
Const MaxRows As Integer = 10000 'my max allowed rows
Dim row As Integer 'stores current row

'start at the first increment and loop through
'increments of 96 up until our max number of rows
row = 96
Do
'---CODE TO INSERT ROW HERE---'
row = row + 96
If row > MaxRows Then Exit Do
Loop
End Function
 
Last edited:
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top