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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
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
 

philhowell

Board Regular
Joined
Jun 11, 2002
Messages
175
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:
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
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.
 

philhowell

Board Regular
Joined
Jun 11, 2002
Messages
175

ADVERTISEMENT

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

mrattana

Board Regular
Joined
Sep 30, 2008
Messages
62

ADVERTISEMENT

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?
 

cazicss

New Member
Joined
May 21, 2010
Messages
1
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:

steveb9990

New Member
Joined
Jun 6, 2009
Messages
41
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!
 

steveb9990

New Member
Joined
Jun 6, 2009
Messages
41
* Bump *

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,094
Messages
5,768,051
Members
425,451
Latest member
JohnBrooksBiddle

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
Top