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_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