VBA - Insert rows below a dynamic header

Question

I am looking to insert a new row below a frozen header row at the top of a spreadsheet. The issue I face is the amount of rows in the header is ever changing but I always want the row to be inserted at the first line below the header. Is there a flag in the row that says its frozen? which I could just count the amount of rows with said flag, add 1 and insert row. Any help would be very helpful.

Matt

When you say frozen, do you mean you used ActiveWindow.FreezePanes (in VBA), or View | Freeze Panes | Freeze Panes (from the ribbon). Or do you mean Split where you have multiple scrollable views of the same spreadsheet in the same window? — FreeMan
– FreeMan, Commented May 14, 2015 at 16:29
I mean View | Freeze Panes | Freeze top row, From the ribbon — themackyo
– themackyo, Commented May 14, 2015 at 16:38

Byron Wall · Accepted Answer · 2015-05-14 16:49:39Z

6

If you are using FreezePanes then I think you go this route:

Sub InsertRowBelowHeader()
    Rows(ActiveWindow.Panes(1).VisibleRange.Rows.Count + 1).Insert
End Sub

Before, the freeze line is below row 5. Freeze pane was done on cell A6

before

After, a row is added to split a/b

after

Here is a relevant discussion which came up on Google for freeze panes and VBA. http://www.mrexcel.com/forum/excel-questions/275645-identifying-freeze-panes-position-sheet-using-visual-basic-applications.html

edited May 14, 2015 at 16:49

answered May 14, 2015 at 16:35

Byron Wall

4,0202 gold badges16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

FreeMan Over a year ago

Excellent! ActiveWindow.Panes(1).VisibleRange.Rows.Count + 1 will give you the row number below the freeze line.

themackyo Over a year ago

My terminology might be incorrect, all this does is inserts a line in the header and increases the amount of rows the header contains by 1

Byron Wall Over a year ago

When I run that code on my end, it adds the row below the freeze line. Maybe add a picture to your post so we can see what we're dealing with?

FreeMan Over a year ago

Your answer solved the question OP asked. His acceptance of the other answer indicates that wasn't the question he meant to ask...

Byron Wall Over a year ago

At least I learned how to access VisibleRange... that's new.

omegastripes · Accepted Answer · 2015-05-14 16:25:39Z

3

Here you are!

Sub InsertRowBelowHeader()
    Rows(ActiveWindow.SplitRow + 1).Insert
End Sub

answered May 14, 2015 at 16:25

omegastripes

12.6k4 gold badges52 silver badges105 bronze badges

5 Comments

FreeMan Over a year ago

Close, but possibly no cigar. The OP asked about .FreezePane which is different and can be set independently of Split. If he's really talking about SplitPanes you nailed it, if not, I don't think it can be done.

themackyo Over a year ago

That just inserts a row at the top of the page inside the header and shifts everything in the header down. I need to add a row outside of the header

themackyo Over a year ago

All I did was go to View > Freeze pane > Freeze top row in excel and then inserted rows on top of that header. I just want to use VBA to along with other things insert a row right below the "header" frozen row. The thing is the amount of rows in the header is always changing but I want the row always inserted right after the header.

omegastripes Over a year ago

I reproduced all actions you described, then launched the code, and I've got a row inserted below the frozen rows. I'm on Excel 2007 Win7 x64.

themackyo Over a year ago

Okay, This worked, I must of had an old piece of code still in there that was inserting a row at the top. Thanks so much!

Collectives™ on Stack Overflow

VBA - Insert rows below a dynamic header

2 Answers 2

5 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related