Forum Discussion

adstristar's avatar
adstristar
Brass Contributor
Aug 31, 2019
Solved

Inserting Rows Automatically

I have created a table and in column A I have the departments and in column B I have item numbers. What i want to be able to do automatically is when I enter an additional number onto column B it ins...
excel
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Sep 01, 2019

    adstristar 

    I have tweaked the code. Please replace the existing code with the following code and let me know if this works as desired now.

     

     

    Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
On Error GoTo Skip:
If Not Intersect(Target, Range("B:B")) Is Nothing Then
    Application.EnableEvents = False
    If Target.Offset(0, -1) <> "" Then
        If Target <> "" Then
            Rows(Target.Row + 1).Insert
            Target.Offset(1, -1) = Target.Offset(0, -1)
        Else
            If Application.CountIf(Columns(1), Target.Offset(0, -1).Value) > 1 Then
                Rows(Target.Row).Delete
            End If
        End If
    End If
End If

Skip:
Application.EnableEvents = True
End Sub

     

     

    If that takes care of your original question, please mark your question as Solved by accepting the Answer.

     

adstristar's avatar
adstristar
Brass Contributor
Sep 01, 2019

Subodh_Tiwari_sktneer  Thank you for your reply. It is very much what I was looking for however if you look on the picture ( hope you can see it) there are certain rows that it will not work on . So by looking at column A row 2 has worked but (row1), row 4,row 7, row10,row 13,row16,row 19, and every third row there on in does not work. I used Row 1 for a heading although I can use the column heading for that. 

 

Is there a way of amending this macro so that it will work with every line? Also can I format this into a table ?Not to worries about the latter though.

 

Subodh_Tiwari_sktneer's avatar
Subodh_Tiwari_sktneer
Silver Contributor
Sep 01, 2019

adstristar 

I have tweaked the code. Please replace the existing code with the following code and let me know if this works as desired now.

 

 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
On Error GoTo Skip:
If Not Intersect(Target, Range("B:B")) Is Nothing Then
    Application.EnableEvents = False
    If Target.Offset(0, -1) <> "" Then
        If Target <> "" Then
            Rows(Target.Row + 1).Insert
            Target.Offset(1, -1) = Target.Offset(0, -1)
        Else
            If Application.CountIf(Columns(1), Target.Offset(0, -1).Value) > 1 Then
                Rows(Target.Row).Delete
            End If
        End If
    End If
End If

Skip:
Application.EnableEvents = True
End Sub

 

 

If that takes care of your original question, please mark your question as Solved by accepting the Answer.

 

  • adstristar's avatar
    adstristar
    Brass Contributor
    Sep 01, 2019

    Subodh_Tiwari_sktneerThank you it resolves my original question yes … am I able to use this in a table as well? Also how do I mark it as solved please?

    • Subodh_Tiwari_sktneer's avatar
      Subodh_Tiwari_sktneer
      Silver Contributor
      Sep 01, 2019

      adstristar 

      Great! Glad it worked as desired.

      Yes, it will work even if the data is in an Excel Table. But right now, the code is inserting the entire row whereas in case of a table only a table row can be inserted instead of inserting entire row. If you have any such requirement, you may open a New Question.

       

      A question is marked as Solved once a post is accepted as an Answer or a Solution and you have successfully done that.

Resources