Announcement

Collapse
No announcement yet.

Insert row automatically

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Insert row automatically



    .
    .

    Hello Masters,

    How to automatically insert a new row after enter a value in the last cell used of column B ?.



    regards,
    Pedro
    .
    .

  • #2
    Re: Insert row automatically

    Hmm. Can you clarify? If you enter a value at the end of values used in a column, that changes the last cell used. 1) Do you then want a row inserted after that? or is it that 2) if the last cell with a value is then changed to another value, you want a row inserted? Or both?
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

    Comment


    • #3
      Re: Insert row automatically

      If your answer to my previous questions was Both, then the following Change macro will work. It should be placed in the sheet module (right click on the sheet tab and select view code).
      Private Sub Worksheet_Change(ByVal Target As Excel.Range)
      If Target.Column <> 2 Or Target.Count <> 1 Then Exit Sub
      If Target.Row = Cells(65536, 2).End(xlUp).Row Then _
      Target.Offset(1, 0).EntireRow.Insert
      End Sub
      Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

      Comment


      • #4
        Re: Insert row automatically

        .
        .

        Derk,

        Thank you so much.
        Works wonderful!!!!!

        best regards,
        Pedro
        .
        .

        Comment


        • #5


          Re: Insert column automatically

          Originally posted by Derk View Post
          If your answer to my previous questions was Both, then the following Change macro will work. It should be placed in the sheet module (right click on the sheet tab and select view code).
          Private Sub Worksheet_Change(ByVal Target As Excel.Range)
          If Target.Column <> 2 Or Target.Count <> 1 Then Exit Sub
          If Target.Row = Cells(65536, 2).End(xlUp).Row Then _
          Target.Offset(1, 0).EntireRow.Insert
          End Sub
          I'm creating a spreadsheet where in one sheet every day information on purchases are inputted and then analysis is done on another page.

          Each row in the first spreadsheet is a purchase and in the second the monthly purchases are summarised in a column. When i add a purchase for the 1st of the month i would like an extra column to be automatically made in the second sheet with the title Aug-11 for example..

          any help would be most appreciated, i'm using excel 2010

          Comment

          Working...
          X