VBA - Weird Split Behavior

Microsoft Excel 2003 (full product)
February 9, 2010 at 15:09:52
Specs: Windows XP
This is probably a simple fix, but I'm tired of fooling with it...

Setup: Ad hoc "database" with primary key in Column Q
Row 1 contains header information, and it is "split" horizontally
Cell Q2 contains a simple =(MAX(Q4:Q1000))+1 formula
Row 3 is a blank row, unformatted

The purpose of the macro is to click/select where a new row needs to go in, then click a button to run the macro - inserting a row, copying the contents of Q2 [my max+1 counter] into my new row, in the 17th column [Q column].

This all worked fine if I manually removed the split, then ran the macro. I decided to jazz it up, adding a step to both remove the split, then re-add it after the counter was placed.

The macro works perfectly the FIRST time. The second time, it adds a new Row 1 and copies the contents of the new Q2 [formerly Q1, before the row addition] and adds the split back. If I rem out the split addition, it continues to work.

FYI, I'm clicking on an entirely new row prior to running the macro the second time, so "activecell" should be the new row I've selected prior to the second macro run, correct?

In summary - What can I do to make it acknowledge my second new row position and/or not think that Row 1 is the place where I want my new row inserted?


Sub InsertRowCopyQ2ToNewRow()

Application.ScreenUpdating = False

With ActiveWindow
.SplitRow = 0
End With

Dim Rng, n As Long, k As Long
Rng = 1
If Rng = "" Then Exit Sub

k = ActiveCell.Offset(0, 0).Row
n = Cells(k, 17).End(xlToLeft).Column

Range(ActiveCell, ActiveCell.Offset(0, 0)).Select
Range(ActiveCell, ActiveCell.Offset(0, 0)).EntireRow.Insert
Range(Cells(k, 17), Cells(k + Val(Rng) - 1, 17)).Formula = Range("Q2").Value

With ActiveWindow
.SplitRow = 1
End With

Application.ScreenUpdating = True
End Sub

See More: VBA - Weird Split Behavior

Report •

February 10, 2010 at 13:24:03
OK, so I figured this out.

If you put a command box above the split, it fails.
If you put it below the split or on a custom menu, it works.

Good to know...

Report •
Related Solutions

Ask Question