Solved: Pass an InputBox value into number of rows

[jrb]

Not sure if this can be consolidated at all, but as it is right now it works. The part I really need help on is how to pass the value entered in the InputBox into the number of rows.


Sub RowsAdd()
Dim rng As Range
Dim NoRows As Variant
Dim LR As Long
LR = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
NoRows = InputBox("How many rows would you like to add?")
With Sheets("Sheet7")
If LR > 8 Then
Set rng = Range("A" & LR & ":" & "E" & LR)
rng.Copy rng.Resize(NoRows + 1)
Range("D" & LR + 1).Resize(NoRows, 2).ClearContents
Else
If IsNumeric(NoRows) Then
Set rng = Range("A8:E" & LR)
rng.Copy rng.Resize(NoRows)
End If
End If
Rows("8:11").RowHeight = 27
End With
End Sub

[Dave]

On 4th edit I'll just repost. Dave

[Bob Phillips]

Sub RowsAdd()
Dim rng As Range
Dim NoRows As Variant
Dim LR As Long
LR = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
NoRows = InputBox("How many rows would you like to add?")
With Sheets("Sheet7")
If LR > 8 Then
Set rng = Range("A" & LR & ":" & "E" & LR)
rng.Copy rng.Resize(NoRows + 1)
Range("D" & LR + 1).Resize(NoRows, 2).ClearContents
Else
If IsNumeric(NoRows) Then
Set rng = Range("A8:E" & LR)
rng.Copy rng.Resize(NoRows)
End If
End If
Rows(8).Resize(NoRows).RowHeight = 27
End With
End Sub

[jrb]

Thanks Bob works great.