Use specific cell instead of ActiveCell

[sparky2205]

Hi folks,
I'm trying to create a macro that will allow the user to input newrows in a protected worksheet based on a starting point and a number ofrows.
The newly entered rows will be copies of the row above where the new rows willbe entered. That row contains formulas which must copy with the rows.
I can get this working using ActiveCell but I don’t want the user to have to selectthe row in the worksheet before running the macro.
So what I want is to allow the user toenter the starting position and the number of rows and based on this informationperform the task.

This is where I’m at but I’m having aproblem assigning the starting position to a variable.

Sub InsertMultipleRows()
Dim s As Variant
Dim n As Variant
Dim r As Range
'Get the starting position
s= Application.InputBox("Enter the starting position", , , , , , , 1 +2)
If s = False Then
Exit Sub
ElseIf s = "" Then
Exit Sub
End If
r= "A" & s
'Get the number of rows to input
n= Application.InputBox("Enter the number of rows to input", , , , , ,, 1 + 2)
If n = False Then
Exit Sub
ElseIf n = "" Then
Exit Sub
End If
'Unprotect the sheet
ActiveSheet.Unprotect Password:=""
'Copy the cell
r.EntireRow.Copy
'Fill down the required numbe of times
Range(r.Offset(1, 0), r.Offset(n, 0)).EntireRow.Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub

I think the problem is with r = "A" & s
I’m getting the “Object variable or Withblock variable not set” error.

Any help would be greatly appreciated.

 

[Fluff]

Try

Set r = Range("A" & s)

 

[sparky2205]

Thanks for the quick reply Fluff. That's perfect.
I was close a couple of times. That's about the only variation I didn't try.

How do I mark this post as solved?

 

[Fluff]

Glad to help & thanks for the feedback.
We don't mark posts as "Solved" here.

 

[sparky2205]

I've just noticed one other thing that is an issue with my setup.
My macro will copy the entire row because of course I've told it to, r.EntireRow.copy.
What if I only want columns M & N to be copied when inserting the new rows?
I thought something like: r.Range("M" & s & ": N" & s).Copy. But this also produces an error.

 

[Fluff]

Do M:N contain formulas or values & are they the only columns that do?

 

[sparky2205]

That is correct. M and N contain simple formulas and are the only columns populated in the sheet.

 

[Fluff]

Ok,try

Range(r.Offset(1, 0), r.Offset(n, 0)).EntireRow.Insert Shift:=xlDown
Rows(s).Resize(n).SpecialCells(xlConstants).ClearContents

 

[sparky2205]

Hi Fluff,
thank a million, this works perfectly.

I hope you'll bear with me while I try to understand this.

The Range pieceis clear.

This is myunderstanding of the line:
Rows(s).Resize.SpecialCells(xlConstants).ClearContents
It would become:
Rows(10).Resize(2).SpecialCells(xlConstants).ClearContents

I’m assuming it’s the xlConstants, the Type in the SpecialCells method, thatprevents the formulas in the newly inserted (i.e. copied) lines getting deleted?
So, if the line had been:
Rows(s).Resize.SpecialCells(Variant).ClearContentsthe behaviour would have been different?

 

[Fluff]

I’m assuming it’s the xlConstants, the Type in the SpecialCells method, thatprevents the formulas in the newly inserted (i.e. copied) lines getting deleted?

That's correct.
If it had been

SpecialCells(xlformulas)

It would have removed the formulas & left the rest.