Macro that Inserts a row in between rows (with formulas etc)

**How How** · 02-13-2013, 08:04 AM

Hi,
I’m new to VBA & struggling a bit…..to explain…..I have 2 worksheets (“Main Log” & “Summary Log”) In the code below it inserts a row where I want it including formulas etc which is good…..but…
1. Is there a code that would insert in the “Main log” & “Summary log” (P.s I need the row in exact same row number but it has different formulas in it)?
2. Is there another code to delete the entire row on both sheets also?
3. Can I get round having the Summary log sheet locked?

#
Sub Test()
Dim r
r = Application.InputBox(Prompt:="Enter row number", Title:="Insert row", Default:=ActiveCell.Row, Type:=1)
If r = False Then Exit Sub
Rows(r - 1).Copy
Rows(r).Insert
On Error Resume Next
Rows(r).SpecialCells(xlCellTypeConstants).ClearContents
End Sub
#

**AB33** · 02-13-2013, 08:28 AM

If you could attach a sample, I can help you with 1 and 2.

**How How** · 02-13-2013, 01:47 PM

Hi, As requested. It would be great if you could get it to insert & delete the same row at a time without affecting the formulas & CF.
Thanks

**AB33** · 02-13-2013, 02:01 PM

How How,
In which columns are the formulas? For e,g, there are formulas in all columns in sheet 2, but in sheet 1, column B is the only one which has a formula. On what bases(Criteria ) you wish to delete the rows?

**How How** · 02-13-2013, 02:51 PM

AB33,
Sheet 1 as you said has 1 column with formula & when a row is added or deleted the formula shouldn't change (ie if I delete manually the sequence of the formula will change & I get an error, #). Sheet 2 is basically just a summary of sheet 1 & again if I delete the sequence changes & I get an error.

In short I want to insert or delete 1 row in both sheets without altering the squence of the formula.

Sheet 1 - Column A, there could be cells with 100,101,102 & column B, a,a,a. But if I wanted to enter another 100 (after the 1st 100) I would insert a row & enter 100 which would make 100 b in column A & B respectively........Hope you understand my gibberish!!

**AB33** · 02-13-2013, 03:03 PM

How How,
Sorry, IMO, it would not make any difference if either insert, or delete a row, whether you do it manually, or by using a code,as the formulas and cells are linked. I am sure others may know more than I do, but from my memory what you need is the Indirect Function.