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.Offset(-1).EntireRow.Select

   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. _

     SpecialCells(xlConstants).ClearContents

    

     Application.SendKeys ("{down}")

 

End Sub

 

 

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