Handy Excel Macros
(Microsoft Excel 2003)

Row formatting Macro

This inserts a new row while formatting the new row like the row above

Steps: -highlight the row you want to insert a line above and run the macro

Sub Row_Format()

Altered by Mark van Zoest so that row is placed above current row selected.

' Documented:  http://www.mvps.org/dmcritchie/excel/insrtrow.htm

' Re: Insert Rows --   1997/09/24 Mark Hill <markhill@charm.net.noSpam>

   ' row selection based on active cell -- rev. 2000-09-02 David McRitchie


   Dim vRows As Long

   vRows = 0

   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


     Selection.Resize(rowsize:=2).Rows(2).EntireRow. _

    Resize(rowsize:=vRows).Insert Shift:=xlDown


    Selection.AutoFill Selection.Resize( _

    rowsize:=vRows + 1), xlFillDefault


    On Error Resume Next    'to handle no constants in range -- John McKee 2000/02/01

    ' to remove the non-formulas -- 1998/03/11 Bill Manville

    Selection.Offset(1).Resize(vRows).EntireRow. _



     Application.SendKeys ("{down}")


End Sub



Back to Index Page
FAQ -- Error 1004
triangle.gif triangle.gif