asked on

Insert Row macro is awesome - except for one little thing...

I found this snippet of code, and it works beautifully for my need to be able to insert a row, maintaining formulas and formats, but clearing constants.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'David McRitchie, 2007-09-07 insrtrow.htm on double-click
'-- will copy more often than Extend Formulas and Format (tools option)
Cancel = True
Target.EntireRow.Copy
Cells(Target.Row + 1, 1).EntireRow.Insert
Cells(Target.Row + 1, 1).EntireRow.Select
ActiveSheet.Paste
Application.CutCopyMode = False
On Error Resume Next
'-- customize range for what cells constants can be removed --
Intersect(Selection, Range("a:IV")).SpecialCells(xlConstants).ClearContents
On Error GoTo 0
End Sub

I would like to make a couple of tweaks:

1. I would like to limit the macro to inserting rows below Row 20. That way, users would be unable to screw up any page formatting in Rows 1-19.

Even better, I'd like to limit the macro to operate on a table. That way, the macro will never be able to run on any rows BELOW the last line of the table.

Related topic: Is there a way to allow users to delete rows, but only within a range of rows? I know I can protect a sheet from row deletion, but it's a global allow/disallow thing.

And finally, can I limit the scrolling to the middle portion only? In this case it would be Ross-Master-Quote---Macro-enabled-ExpertRow 21-24 (plus any additional inserted rows above Row 24.)

I've attached a copy of the file, with personal info redacted.

Thanks!

Rob Henson

Above this line:

Target.EntireRow.Copy

Insert this:

If Target.Row < 20 Then Exit Sub

I will keep thinking about the second tweak, it will just be an OR statement within the above line but will need to determine last row of table first.

Thanks
Rob H

eracer

ASKER

The last row is going to be tricky, since adding rows will increment the last row's number.

The 'If Target Row...' statement worked perfectly. Thanks!
I'm going to put a warning on the sheet for users not to run the macro on any rows below the new rows that pop up.

eracer

ASKER

For some reason, I didn't notice that protecting the worksheet causes a [Run-time Error '1004': Insert method of Range class failed.] when the 'Insert Row' macro runs.

I need to be able to protect the worksheet, but I need my people to be able to use the event macro.

Enabling users ability to 'Insert Rows' in a protected workbook fixes the problem, but I'm hoping to avoid giving them that ability.

I saved the worksheet to C:\users\%user%\appdata\roaming\microsoft\templates (since this is a trusted location, and will run the macro without prompting. Even so, the error occurs whether I open the template, or the sheet in my documents folder (which does prompt to enable the macro.)

It doesn't matter whether the individual cells are locked or unlocked. If the sheet is protected, the macro fails. If it's unprotected, the macro runs.

Here's the Debug:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'David McRitchie, 2007-09-07 insrtrow.htm on double-click
'-- will copy more often than Extend Formulas and Format (tools option)
Cancel = True
If Target.Row < 24 Then Exit Sub
Target.EntireRow.Copy
'.................The following line is highlighted yellow, with an arrow pointing at it.................
Cells(Target.Row + 1, 1).EntireRow.Insert
'..................No other lines are highlighted..............
Cells(Target.Row + 1, 1).EntireRow.Select
ActiveSheet.Paste
Application.CutCopyMode = False
On Error Resume Next
'-- customize range for what cells constants can be removed --
Intersect(Selection, Range("a:IV")).SpecialCells(xlConstants).ClearContents
On Error GoTo 0
End Sub

SOLUTION

J2F

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eracer

ASKER

Thanks J2F.

I know a little bit about Excel, but I know nothing about VBA. Amazing how the internet can make me look educated, when I'm actually ignorant. I'm learning a little bit from experts here, and my boss is saying, "Oooooo, how did you know how to do THAT?" ;)

I'm going to look up the code for adding the 'Protection off/on' to the macro.

In my case, I want the cursor to stay on the last row inserted. It would be nice to have it move two columns over, however.

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

J2F

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eracer

ASKER

Thanks J2F.

I need to spend some time learning more about Excel (and VBA) for sure. Right now I'm tasked with getting a worksheet done that meets the bosses needs. Having resources like you guys is great, and I'm learning some as I go.

J2F

Part of what I know about Excel, I've learned by studying other people's code. (I learn a lot from this forum)
A little of what I know about Excel has been gleaned from books.
The rest of what I know about Excel is from "trial and error".

Hang in there. The level of your expertise in Excel is indirectly proportional to how much sleep you get :)