Adding rows VBA

[erniepoe]

Hi all,

I have some code to add rows to the bottom of a spreadsheet. It asks the user how many rows they want to add, and then puts them in. I want it to find the last row of data and put in new rows above that, but it's finding the last row of data and inserting the new rows below that.

Not sure what the fix is here, anybody see something obvious? Thanks for any help.


-------------------------------------



Sub AddRow()

ActiveSheet.Unprotect Password:="Thirdparty"

Dim vRows As Long
Dim sht As Worksheet, shts() As String, i As Long

Cells.Find(What:="New Site", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

'ActiveCell.Offset(-1, -1).Select

ActiveCell.EntireRow.Select
vRows = _
Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1)

If vRows = False Then Exit Sub

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

Selection.Offset(1).Resize(vRows).EntireRow. _
SpecialCells(xlConstants).ClearContents
Next sht

Worksheets(shts).Select


ActiveSheet.Protect Password:="Thirdparty"

End Sub

 

[erniepoe]

trying this again, anybody have any epiphany's?

 

[kpark91]

Try

Sub AddRowKPARK()
   Dim LR&, numAR&, i&
   LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row 'Get Last Row (requires no blank cells in column A) You can use another method to find the last row
 
  numAR = InputBox("How many rows do you want to add?", "Add Rows", 1)
   For i = 1 To numAR
       ActiveSheet.Range("A" & LR).EntireRow.Insert
   Next i 
End Sub

 

[erniepoe]

thanks kpark,

did you mean for this to be in lieu of everything i posted, or just take the place of certain spots in that code?

much appreciated.