Excel Auto Line Inserts

  osben 10:43 05 Mar 2004


Excel XP2002 - I want to be able to put figures into cells going from top to bottom and have a "totals" cell at the bottom of these. When I reach the last cell to insert a figure I want another line to be automatically inserted
and the formula in the "totals" cell to take account of that line insertion.

Is it possible an if so how. Any Ideas


  Cook2 11:37 05 Mar 2004

Enter your numbers into A1, A2, A3 ........etc

If the number of entries is, say 12, then in A13 type =B14 (assuming all entries are of equal length)

In A14 type =sum(A1:A13) Which will automatically adjust the total when you have completed column B.

It's much easier Done that Said.

  rogertjj 11:59 05 Mar 2004

To do this, you need to use some VBA code.

Presuming that you are going to enter figures only, on Sheet1, type "Total" in Cell A3. Then, press ALT + F11 to enter the VBA editor. On the left hand side, double click "Sheet1(Sheet1)" and paste the following code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

rng = ActiveCell.Address

If ActiveCell.Offset(1, 0) = "Total" Then

Rows(ActiveCell.Row + 1).EntireRow.Insert

ActiveCell.Offset(2, 1) = "=SUM(A1:A" & ActiveCell.Row & ")"

ActiveCell.Offset(1, 1) = ""

End If

End Sub

Select cell A1 on sheet1, and start typing numbers, and the Total should be automatically be calculated. This is a bit rough, and VOG may well be able to provide a better answer.

  rogertjj 12:01 05 Mar 2004

you don't need the rng = Activecell.Address line. Just me being stupid !

  Eric10 13:09 05 Mar 2004

I'm not an Excel expert but I came up with this while playing with your problem and offer it as a possible basis for further development. It's not exactly what you asked for but may serve your purpose.

If you want to enter your numbers in column A and are prepared to accept your total in Column B level with the last entry, then this formula


typed into B1 and copied down to the end of your sheet will give a running total in column B adjacent to your last entry in column A with other cells in column B remaining blank.

  VoG II 22:21 05 Mar 2004

The following macro will automatically produce row and column totals with an extra row and column inserted to separate them. Simply click anywhere in the data table and run the macro. Adapt to suit!

Sub TotalThem()

Dim rRange As Range, rTotal As Range, cTotal As Range

Set rRange = ActiveCell.CurrentRegion

Set cTotal = rRange.Offset(0, rRange.Columns.Count).Columns(2)

Set rTotal = rRange.Offset(rRange.Rows.Count).Rows(2)

rTotal.Font.Bold = True

cTotal.Font.Bold = True

Cells(rTotal.Row, cTotal.Column).Font.Bold = True

Range(Cells(rTotal.Row, rTotal.Column), Cells(rTotal.Row, rTotal.Column + rTotal.Columns.Count + 1)).BorderAround Weight:=xlMedium

Range(Cells(cTotal.Row, cTotal.Column), Cells(cTotal.Row + cTotal.Rows.Count + 1, cTotal.Column)).BorderAround Weight:=xlMedium

Cells(rTotal.Row, cTotal.Column).BorderAround Weight:=xlThick

rTotal.Formula = "=Sum(" & rRange.Columns(1).Address(False, False) & ")"

cTotal.Formula = "=Sum(" & rRange.Rows(1).Address(False, False) & ")"

Cells(rTotal.Row, cTotal.Column).Formula = "=Sum(" & cTotal.Address & ")"

End Sub

  Megatyte 10:45 06 Mar 2004

Why not have your Totals at the top of the column and freeze it? All you need to do then is total the entire column, apart from your totals cell.


  osben 10:15 08 Mar 2004

Thanks for all your suggestions.

They work!!


  Megatyte 17:42 08 Mar 2004

Which one did you use?


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5T review: Hands-on

Illustrator Andrés Lozano on his improv line work, brazen use of colours & hand sketching

iPhone X review

Comment envoyer gratuitement des gros fichiers ?