Thread: Macro to Insert Cell (Not Row)

Hi All! I found multiple codes for inserting a row but not a cell. I have the following code which does its job of adding a row...however, when I delete .EntireRow, it still adds a row. Something's not working...I need your help!

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)

   Cancel = True
   Dim x As Long
   ActiveCell.EntireRow.Select  'So you do not have to preselect entire row
   If vRows = 0 Then
    vRows = Application.InputBox(prompt:= _
      "How many rows do you want to add?", Title:="Add Rows", _
      Default:=1, Type:=1) 'Default for 1 row, type 1 is number
    If vRows = False Then Exit Sub
   End If

   'if you just want to add cells and not entire rows
   'then delete ".EntireRow" in the following line

   Dim sht As Worksheet, shts() As String, i As Long
   ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _
       Windows(1).SelectedSheets.Count)
   i = 0
   For Each sht In _
       Application.ActiveWorkbook.Windows(1).SelectedSheets
    Sheets(sht.Name).Select
    i = i + 1
    shts(i) = sht.Name

    x = Sheets(sht.Name).UsedRange.Rows.Count 'lastcell fixup

    Selection.Resize(rowsize:=2).Rows(2). _
     Resize(rowsize:=vRows).Insert Shift:=xlDown

    Selection.AutoFill Selection.Resize( _
     rowsize:=vRows + 1), xlFillDefault

    On Error Resume Next
    Selection.Offset(1).Resize(vRows).EntireRow. _
     SpecialCells(xlConstants).ClearContents
   Next sht
   Worksheets(shts).Select

End Sub

Thanks, Chris

Hi,

Please take a moment to familiarise yourself with the forum rules and edit your post to include code tags.

Dom

Sorry about that Dom! It's all fixed. I also posted on Ozgrid (http://www.ozgrid.com/forum/forumdisplay.php?f=8). I'll post the solution here if someone finds one there. I appreciate everyone's time!