Macro to insert and delete rows

J

Jonsson

Hi, I need help with two macro´s.

One that insert rows and one that delete rows.

I have found this one at David Mcritchie´s webpage to insert in active sheet
How to get the same, but delete instead of insert? And how to change to
several sheets?
Is it just to change certain commands in this to get what I´m looking for?

I´m new to this, but I think it´s very interesting. I hope someone can help
me to understand this a little bit better.

//Thomas

Sub InsertRowsAndFillFormulas(Optional vRows As Long)
' Documented: http://www.mvps.org/dmcritchie/excel/insrtrow.htm
' Re: Insert Rows -- 1997/09/24 Mark Hill
'Dim vRows As Integer
' row selection based on active cell -- rev. 2000-09-02 David McRitchie
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows <> 1 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) '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

'rev. 20001-01-17 Gary L. Brown, programming, Grouped sheets
Dim sht As Worksheet, shts() As String, i As Integer
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

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
Next sht
Worksheets(shts).Select

End Sub
 
D

David McRitchie

Wrong macro to be working with to delete rows.
You would be better off starting at
http://www.mvps.org/dmcritchie/excel/insrtrow.htm#deleter
for deleting rows. But the insert row macro does have the
code to go through the grouped sheets so that is why you
probably identified that one..

You do not say what you want to delete, or how you want
to select which sheets to process -- grouped sheets is
one way. Unless you can delete your rows with one
instruction you normally start from the bottom to examine
and delete the rows you do not want. Also see
http://www.mvps.org/dmcritchie/excel/delempty.htm

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Inserting Row 3
Insert letter in cell 2
Insert row and copy formulas 5
Add a new code macro 0
Compare master list and update 2
Add New Students 2
Macro Help 0
Macro working in Excel 2003; not working in Excel 2000 3

Top