The UK's Number 1 for Microsoft Office Training

Sitemap

Bookmark page

Printable version

Course updates
due to adverse
weather conditions

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Inserting a new row in a particular place

	98.3 % recommend us
	More live stats...

Bookmark this page

Share this with a friend or colleague:

The UK's most regular
instructor-led training courses.

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions

Inserting a new row in a particular place

Resolved · Medium Priority · Version 2003

ReplyThu 22 Jul 2010, 12:24Delegate Samantha said...

Inserting a new row in a particular place

I have a password protected spreadsheets that users input data into. The data area has a number of rows and then a protected row at the bottom which has formulae in to tal the above data.

I'd like to write a code so that the user of a spreadsheet can insert a new row by clicking a button and it adds the row at the bottom of the data area but above the total row and also updates the total row formulae to include the new row!

Does this sound possible ad any pointers on where to start?

For upcoming training course dates see: Pricing & availability

ReplyThu 22 Jul 2010, 14:33Trainer Stephen said...

RE: Inserting a new row in a particular place

Hi Samantha

Thanks for your question

You can insert a row at the bottom of your data with the following
code



Dim intLastRow

    

   intLastRow = sheets(1).Range("A1").CurrentRegion.Rows.Count

    

    Sheets(1).Rows(intLastRow).Insert

This imply calculates the last row number and inserts a row there. If the last row contains formulae, then they will update automatically

Regards

Stephen

Thu 29 Jul 2010: Automatically marked as resolved.

Excel articles

· A Beginners Guide To Formulas In Excel
· How To Convert Text To Columns In Excel
· Discover The Difference Between Line And XY Scatter Charts In Excel
· How To Link Between Spreadsheets In Excel
· Make A Custom Sort List In Excel

Please browse our web site to find out more about
excel computer training and other Microsoft training courses.

Excel tip:

Fill formulae across a sheet

To copy a formula down a spreadsheet where there is data underneath, to the left or to the right of the formula, double-click on the fill handle. The fill handle is the little black cross that appears in the bottom right-hand corner of the formula cell. Unfortunately, no similar facility exists to copy formulae across the sheet.

One reasonably quick way to copy an existing formula across a sheet is to select the formula and the cells on the right to which you want to copy it. Then press Ctrl+R to copy the formula across the selected range, or, if you are menu-minded, use the Edit|Fill|Right command.

View all Excel hints and tips

Can i change a macro if i did not create it?

» Forum post: Macros

Differences In Excel 2003, 2007 And 2010

If you're thinking about an upgrade to Excel 2010, then it's worth apprising yourself of the differences beforehand. Will it be worth it? Find out about these new features and understand what the terms mean before you make your decision to upgrade.

» Article: Microsoft training london

Rate this page: