I'm using the macro mentioned in the subject line. I have it set to insert 1 row when the worksheet is opened. I need some code to put in the macro that will not allow a row to be inserted if a blank row already exists. Can you help me, please

Leslie Barberie

Hi Leslie, (This thread is longer than it looks, CDO does not prefix with )

I have updated my page
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
with changes to InsertRowsAndFillFormulas and to the documentation.
What appears below for your answer is also included on that page.
The optional parameter may not have been working previously, it does now.

Note that there is an optional parameter that can be used from another macro.
It would be best to keep the macro as is for general use, but when calling from
a macro to have the calling macro include the number of rows to be inserted
so that the MsgBox will not appear.

Since you do not want to update if the row has already been inserted the
modifications to the calling macro include:
..
If column A of the current row or column A of the next row is empty
the InsertAndFillFormulas macro will *not* be invoked. My test
is with a double-click event macro you should place something similar
to the internal code into your macro.
to it in your macro.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'-- Code example to prevent reinserting if the current row
'-- or next row row has Column A cel empty (testing with TRIM).
Dim x As Long
Cancel = True 'turn off Edit, mode applicable to "Edit directly in a cell"
x = ActiveSheet.UsedRange.Rows.Count 'may alleviate some lastcell problems
Target.Offset(1, 0).EntireRow.Interior.ColorIndex = xlNone '--Testin
If Trim(ActiveCell.Offset(0, 1 - ActiveCell.Column)) = "" _
Or Trim(ActiveCell.Offset(1, 1 - ActiveCell.Column)) = "" Then
MsgBox "skipping due to formula in column C and nothing in Column A --testing"
Exit Sub
End If
Application.Run "pesonal.xls!InsertRowsAndFillFormulas", 1
Target.Offset(1, 0).EntireRow.Interior.ColorIndex = 36 '--Testing
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Leslie Barberie" wrote ...
I'm using the macro mentioned in the subject line. I have it set to
insert 1 row when the worksheet is opened. I need some code to put in
the macro that will not allow a row to be inserted if a blank row already exists.
Can you help me, please?

As noted earlier change the macro that invokes rather than the general
purpose macro.