VBA to Insert a New Row In a Worksheet - Extra Code Needed!

[Simonc64]

OK so I have found this very useful code which Ive attached to a button called "Add New Task" and it works fine BUT......its not entirely what i want to do, so its over to you guys to add some (hopefully!) simple coding to finish it off.

Sub InsertRowsAndFillFormulas_caller()
Call InsertRowsAndFillFormulas
End Sub

Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0)
Dim x As Long
ActiveCell.EntireRow.Select
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How Many Tasks 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

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
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
End Sub


This code 'randomly' inserts a row(s) based on the position of the cursor in the workbook; what I need is to force the cursor to the correct location in order that the new row is inserted in the right place.


Therefore I need some code that follows this logic.........

Find last non blank cell in range B1:B100 and place cursor in this cell

Is this at all possible?

Cheers

Simon

 

[dimexi]

is this what you need?

Option Explicit
Dim last As Long

Sub insert()
last = Cells(Rows.Count, "b").End(xlUp).Row
Range("B" & last + 1).EntireRow.insert

End Sub

 

[Simonc64]

Thanks for your reply dimexi I am testing it out right now. I think it probably does what im after, i just need to 'embed' it into the original code, as that also copies down formulae that exist.

 

[Peter_SSs]

.. or possibly even this single line?

Rows(Range("B" & Rows.Count).End(xlUp).Row + 1).Insert

 

[Simonc64]

Thanks for your input too Peter. All do as I want, but I have chosen to add the additional code to my original code as this also copies any formula on the row down into the newly inserted row too. I dont profess to understand all of it but at the moment Im happy it works - thanks to both of you!

Simon

 

[Simonc64]

Thanks for your input too Peter. All do as I want, but I have chosen to add the additional code to my original code as this also copies any formula on the row down into the newly inserted row too. I dont profess to understand all of it but at the moment Im happy it works - thanks to both of you!

Simon