Page 1 of 4 123 ... LastLast
Results 1 to 10 of 34
Like Tree1Likes

VBA to insert row / colum

This is a discussion on VBA to insert row / colum within the Excel Questions forums, part of the Question Forums category; Hi guy, can't believe its been a year since i last posted on here.... i have been asked to write ...

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Posts
    175

    Default VBA to insert row / colum

    Hi guy,

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

    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

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,008

    Default Re: 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

  3. #3
    Board Regular
    Join Date
    Jun 2002
    Posts
    175

    Default Re: VBA to insert row / colum

    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.


  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,008

    Default Re: VBA to insert row / colum

    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.

  5. #5
    Board Regular
    Join Date
    Jun 2002
    Posts
    175

    Default

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

  6. #6
    New Member
    Join Date
    Jun 2009
    Posts
    9

    Default Re: VBA to insert row / colum

    Hi,

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

  7. #7
    New Member
    Join Date
    Sep 2008
    Posts
    45

    Default Re: VBA to insert row / colum

    Quote Originally Posted by Tom Urtis View Post
    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?

  8. #8
    New Member
    Join Date
    May 2010
    Posts
    1

    Default Re: VBA to insert row / colum

    Quote Originally Posted by mrattana View Post
    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 by cazicss; May 21st, 2010 at 11:23 AM.

  9. #9
    New Member
    Join Date
    Jun 2009
    Posts
    41

    Default Re: VBA to insert row / colum

    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!

  10. #10
    New Member
    Join Date
    Jun 2009
    Posts
    41

    Default Re: VBA to insert row / colum

    * Bump *

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

Page 1 of 4 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com