Sub insertMultipleRows()
'code disables excel while macro runs'
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim rowcount As Integer
rowcount = Application.InputBox(Prompt:="how many rows do you want to insert" & ActiveCell.Row & "?", Type:=1)
'handle error for instances when row count may be negative or zero'
If rowcount <= 0 Then End
Rows(ActiveCell.Row & ":" & ActiveCell.Row + rowcount – 1).Insert Shift:=xlDown
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
-
1what is the question?Max– Max2022-02-02 11:59:18 +00:00Commented Feb 2, 2022 at 11:59
-
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.Dragonthoughts– Dragonthoughts2022-02-11 17:09:58 +00:00Commented Feb 11, 2022 at 17:09
Add a comment
|
1 Answer
LOL. Spent ages scratching my head on this one. As I saw it there was no reason why this code wouldn't work. I copied your code into VBA and the below line should up red:
Rows(ActiveCell.Row & ":" & ActiveCell.Row + rowcount – 1).Insert Shift:=xlDown
For the life of me I couldn't see why and it didn't make any sense.
I deleted the line and manually typed it and everything worked fine! What the ....
Decided to paste the line from above under the line I typed and this is what I got:
Curiouser and curioser! I pasted both line into Text Comparer https://text-compare.com/ and there it was! You're using the wrong minus Character. What you're using is the ASCII 150 Character instead of the standard ASCII 45 character.
Here's the proper code:
Rows(ActiveCell.Row & ":" & ActiveCell.Row + rowcount - 1).Insert Shift:=xlDown