insert new row automatically

[maxx21]

I created a form in which I can enter data that is totaled at the end. Is there a possibility that a new row is inserted at the end of the form automatically every time I enter data in the last field?

 

[joker961]

hey guys i'm still using these codes :
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
Dim rDelete As Range
If Not Intersect(Target, Range("TotalVal").EntireColumn) Is Nothing Then


Application.EnableEvents = False
For Each rCell In Intersect(Target, Range("TotalVal").EntireColumn).Cells


If rCell.Row = Range("TotalVal").Row - 1 Then
If Len(rCell.Value) > 0 Then
Range("TotalVal").EntireRow.Insert
Else
If rDelete Is Nothing Then
Set rDelete = rCell
Else
Set rDelete = Union(rDelete, rCell)
End If
End If
ElseIf Len(rCell.Value) = 0 Then
If rDelete Is Nothing Then
Set rDelete = rCell
Else
Set rDelete = Union(rDelete, rCell)
End If
End If


Next rCell
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
Application.EnableEvents = True


End If


End Sub


but the prob is it worked only on 1 column, and i if have a column below it, it won't work properly
wen i remove and add (the second total is called = totalval1

here's an example
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl63"] [/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]23[/TD]
[/TR]
</tbody>[/TABLE]

removing the last number of the sum
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]18

readding a number
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]12[/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] [/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]7[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]25

you can see the 2nd sum did't leave a spate[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[joker961]

in another way said i need to do this in the same column for multiple time (totalval , totalval1 , totalval2 ....... )there 's something to be change in the codes.

 

[jaymakali]

I want to make a excel database file. Here I have one sheet for data. Second sheet there is a summery format.

What I want to do..

1. I will enter customer data in data sheet. Here one customer details will be multiple times for different product purchase. It will not be for double entry of same product. So sum is not needed.
2. Now I will go to format sheet and select customer name in drop down list and all details from data sheet will appear in format fields.
3. In my sheet I have selected “mihir” customer name and it has two entries, so it will add two rows. If I select other customer with one entry then will add one row.

If possible formula add rows auto then good. Otherwise I will give enough rows in format only data reflection in those rows according to customer name selected will be also a good for me.

thanks pl. help....

 

[NewUser1970]

Amended code as the previous version doesn't always delete all rows properly:

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim rCell                       As Range
   Dim rDelete                     As Range
   If Not Intersect(Target, Range("TotalVal").EntireColumn) Is Nothing Then

      Application.EnableEvents = False
      For Each rCell In Intersect(Target, Range("TotalVal").EntireColumn).Cells

         If rCell.Row = Range("TotalVal").Row - 1 Then
            If Len(rCell.Value) > 0 Then
               Range("TotalVal").EntireRow.Insert
            Else
               If rDelete Is Nothing Then
                  Set rDelete = rCell
               Else
                  Set rDelete = Union(rDelete, rCell)
               End If
            End If
         ElseIf Len(rCell.Value) = 0 Then
            If rDelete Is Nothing Then
               Set rDelete = rCell
            Else
               Set rDelete = Union(rDelete, rCell)
            End If
         End If

      Next rCell
      If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
      Application.EnableEvents = True

   End If
End Sub

Hi Rory, this is perfect for adding and deleting rows automatically. But how to add the formula from the previous row into this new row?

Liza

 

[RoryA]

Which formula?

 

[NewUser1970]

Which formula?

Lets say that there are formula on each column, c1=a1+b1, d1=c1*5% - is there a way to copy this formula (from the preceding row) into the newly added rows? I tried converting the spreadsheet into table hoping it would copy automatically, but didn't work. Please help.