How can I add or delete lines in multiple Excel sheets with VBA

Question

I've got an Excel file, where I have to enter names in one sheet, and then the other sheets are adding or deleting rows.

For example I have the excel sheets User, NumberSheet and GradeSheet.

I am entering "John Doe", "Jane Doe" and "Phil Doe" into the User-Sheet, and in NumberSheet and GradeSheet, 3 Rows are added.

I have this code for now:

Private Sub Worksheet_Change(ByVal Target As range)
Dim KeyCells As range

' The variable KeyCells contains the cells that will
' cause an alert when they are changed.
' Start Cell is A2 because Names start there
Set KeyCells = range("A2:A100")

If Not Application.Intersect(KeyCells, range(Target.Address)) _
       Is Nothing Then

' Display a message when one of the designated cells has been
    ' changed.
    ' Place your code here.
    
    'The row in the user-Sheet, where I added or removed a user
    targetRow = Target.row - 1
    'In the Number Sheet, the row from where the names should get listed
    targetRowInNumberSheet = targetRow
    
    'The value from the cell where I entered the name
    vlue = Target.Value
    
    If IsEmpty(vlue) Then
      'Delete row in Worksheets
      Worksheets("NumberSheet").Rows(targetRowInNumberSheet).Delete
      Worksheets("GradeSheet").Rows(targetRowInNumberSheet ).Delete
    Else
      'Add row in Worksheets
      Worksheets("NumberSheet").Rows(targetRowInNumberSheet).Insert
      Worksheets("GradeSheet").Rows(targetRowInNumberSheet ).Insert
    End If
End If
End Sub

Now this code works if I add a name, delete one or insert a row somewhere in between. But I can't delete multiple names, or delete a row. In both cases IsEmpty(vlue) will be false, and I don't know why

Where, in your code, do you try to "delete multiple names"?

FaneDuru
– FaneDuru

2020-12-04 12:30:25 +00:00
Commented Dec 4, 2020 at 12:30 — FaneDuru
– FaneDuru, Commented Dec 4, 2020 at 12:30
Can your Target range contain more cells?

FaneDuru
– FaneDuru

2020-12-04 12:45:39 +00:00
Commented Dec 4, 2020 at 12:45 — FaneDuru
– FaneDuru, Commented Dec 4, 2020 at 12:45

FaneDuru · Accepted Answer · 2020-12-04 13:03:44Z

1

IsEmpty does not check if a cell value is empty...

It checks if a variable has been initilized.

Instead of If IsEmpty(vlue) Then, simple please use:

If vlue = "" Then

or:

if vlue = Empty Then 'this is different!

Please, check here how Microsoft describes this function...

Then, it is recommended to declare all used variables (targetRow, targetRowInNumberSheet, vlue). If your Target have to contain only one cell, you should add a code line:

If Target.count>1 then Exit Sub

If you want allowing the multiple cells range, you should state it and deal in a different way with what you try doing. I asked about that, but you did not answer anything. If so, i can show you how to proceed.

edited Dec 4, 2020 at 13:03

answered Dec 4, 2020 at 12:25

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

senior_freshman · Accepted Answer · 2020-12-04 12:32:05Z

0

Thats because your Input parameter of the Worksheet_Change Function is a Range called Target.

A Range is an area instead a single Value. If you delete now multiple Rows, the Target.Row property only returns the first Row in your Target Range and so only the First entry will be deleted.

So you should further seek information about Ranges.

I would try this: Count all Rows in your Target Range:

Target.Rows.Count

With this number you can go on from the first Row Index of the Range like in your example with a loop or something:

 targetRow = Target.row - 1

answered Dec 4, 2020 at 12:32

senior_freshman

19113 bronze badges

Collectives™ on Stack Overflow

How can I add or delete lines in multiple Excel sheets with VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related