VBA Why does Delete Shift Up delete everything below it?

Question

So my code whenever a name is added on a sheet then adds the name to all other sheets, and whenever a name is deleted from the same sheet it should be deleted from all other sheets (those are the selected sheets below). But for some reason running ActiveCell.EntireRow.Delete Shift:=xlUp deletes everything below ActiveCell too? Here is my entire code currently.

Private Sub Worksheet_Change(ByVal Target As Range)
    Const cCol As String = "A"
    Const fRow As Long = 2
    
    Dim crg As Range
    Dim ddFound As Range
    Dim ws As Worksheet
    Dim sh As Worksheet
    Dim outpt As String
    Dim i As Integer

    Set crg = Worksheets("Statistics").Columns(cCol).Resize(Rows.Count - fRow + 1).Offset(fRow - 1)
    Dim irg As Range: Set irg = Intersect(crg, Target)
    Dim sraddress As String
    Dim statdel As Range
    
    Dim dws As Worksheet
    Dim ddcrg As Range
    Dim statrange As Range
    
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        sraddress = CStr(irg.Value)
        
        Application.ScreenUpdating = False
        Application.EnableEvents = False

        For Each ws In ActiveWorkbook.Worksheets
            Set ddcrg = ws.Columns(cCol)
            Set ddFound = ddcrg.Find(sraddress, , xlValues, xlWhole)
            
            If sraddress <> "" Then
                irg.Select:   ActiveCell = irg.Value2
                irg.Copy
                ws.Range(irg.Address) = irg.Value2
                Application.CutCopyMode = False
            ElseIf sraddress = "" Then
                Dim Deladdrs As String
                irg.EntireRow.Select
            
                On Error Resume Next

                Sheets(Array("Statistics", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")).Select
            
                Deladdrs = ActiveCell.Address(0, 0)
                
                ActiveCell.EntireRow.Delete Shift:=xlUp
        
                Application.CutCopyMode = False
            Else
            End If
        Next ws
    
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    Else
    End If
End Sub

Your `irg` always refers to the sheet it originates from. You loop over all those sheets with `For`. For each sheet, you delete the same `irg` range, as many times as you have sheets. After each deletion the rows shift one up, so next time you delete, you delete the next row that now occupies the space of the just deleted row. Please see https://stackoverflow.com/q/10714251/11683 and start by removing all those Selects. — GSerg, Jan 17 '22 at 11:15
Oh, thats what you get when you take breaks huh, you're exactly right that was an oversight on me. — Drawleeh, Jan 17 '22 at 11:36
When you say **name** I suppose you do not refer to a Named range. It is only a matter of a string. Is this understanding correct? Then aren't the sheets needed to iterate through the ones selected with the code ` Sheets(Array("Statistics", "January", "February"...`? If so, why iteration? Anyhow, `ActiveCell.EntireRow.Delete Shift:=xlUp` delete the `Target.row` and after the first deletion the row replacing it. Please, try better describing **in words** what you need accomplishing. — FaneDuru, Jan 17 '22 at 11:48
Should we understand that those names, in all involved sheets, **would be on the same rows**? — FaneDuru, Jan 17 '22 at 11:51
The names are strings yes, all that I'm looking to happen is when a string is deleted from the sheet "Statistics", that cell address is then used to delete the same row on every other sheet. I.E. Delete A4 on "Statistics" then it deleted 4:4 on all sheets shift up. — Drawleeh, Jan 17 '22 at 11:56
OK, and doing the same when a new name is inserted/replaced? For the sheets on the array and for other sheets, too? And the event is running in sheet `Statistics". Correct? — FaneDuru, Jan 17 '22 at 12:10
The code that inserts the string in the same cell address on every other sheet when it is input in 'Statistics' works fine, its the first part of the if statement. The code does run in Statistics yes. — Drawleeh, Jan 17 '22 at 12:24
It doesn't matter... I asked if the synchronization must be made between "Statistics" sheet and **only the above mentioned sheets array**. Then, since you try deleting the rows containing the deleted name, **wouldn't it be wise to also delete the `Target` row**? Otherwise, the correspondence between the rows will be lost... — FaneDuru, Jan 17 '22 at 12:27

FaneDuru · Accepted Answer · 2022-01-17T14:44:02.760

1

Please, test the next code. It assumes that only the sheets in the array (from your code) should be updated. And also, in case of a name deletion, the row containing it should also be deleted. The code also covers the case of row deletion, which otherwise should place the value in A:A of the new Target on all row of the sheets to be updated:

Private Sub Worksheet_Change(ByVal Target As Range)
    Const cCol As String = "A", fRow As Long = 2
    
    Dim crg As Range, ws As Worksheet
    Dim irg As Range, irgVal As String

    If Target.Rows.Count > 1 then exit sub. Not allowed more rows to be deleted, changed.
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        Set crg = Me.Columns(cCol).Resize(rows.Count - fRow + 1).Offset(fRow - 1)
        Set irg = Intersect(crg, Target)
        irgVal = irg.value
        If Target.Columns.Count = 16384 Then irgVal = "" 'for the case of deleting the whole row!
        Application.ScreenUpdating = False
        Application.EnableEvents = False

        For Each ws In Worksheets(Array("January", _
                               "February", "March", "April", "M)
            
            If irgVal <> "" Then
                ws.Range(Target.Address).value = irg.value
            Else
                ws.rows(irg.row).EntireRow.Delete
            End If
        Next ws
        If CStr(irg.value) = "" Then irg.EntireRow.Delete 'delete also the Target row...

        Application.ScreenUpdating = True
        Application.EnableEvents = True
    End If
End Sub

The above code is not designed for multiple rows deletion, change

An event triggering Row deletion (at Ribbon level) can be designed, but it does not make the object of the question...

edited Jan 17 '22 at 14:44

answered Jan 17 '22 at 12:48

FaneDuru

38,298
4
19
27

The above drops the 'Object Required' error on ```ws.Rows(irg.Row).EntireRow.Delete``` when deleting string from statistics – Drawleeh Jan 17 '22 at 13:09
The error pops up when the code goes to the second sheet 'January' and Irg no longer has an object – Drawleeh Jan 17 '22 at 13:19
@Drawleeh Did you use the above code **as it is**? `If CStr(irg.value) = "" Then irg.EntireRow.Delete` code line is outside the iteration! – FaneDuru Jan 17 '22 at 13:23
@Drawleeh ??? Can you answer my clarification question, please? The code is tested on my computer and does not raise any error. But it does in your environment, I can find another way to deal with it. So, I need your feedback if you really need help... – FaneDuru Jan 17 '22 at 14:26
Yes, code is used exactly as is, the line within the loop is throwing up error. It never reaches the line outside of the loop – Drawleeh Jan 17 '22 at 14:33
@Drawleeh Firstly, that line **does not delete a row from Statistics**. Then, are there any **protection** involved in the sheet to be processed? If not something confidential, can you share the workbook you are working on? At least, a dummy one... The code works well on my testing workbook. Ups... Statistics must not be in the array. I will adapt the code. – FaneDuru Jan 17 '22 at 14:36
The string in statistics is deleted by the user, when it is deleted the error is thrown up after it loops through once when ```irg``` becomes nothing in ```ws.rows(irg.row).EntireRow.Delete``` . Nothing is protected – Drawleeh Jan 17 '22 at 14:40
@Drawleeh I tested the code **without Statistics in the aray**. Then I've just copied your used sheets array and did not delete Statistics, as it should. I edited my previous comment and specify that... I will delete it from the above answer, too. Please, test it after that. In less than a minute... Did it! – FaneDuru Jan 17 '22 at 14:42
Okay, without "Statistics" it works in the array then. Thank you – Drawleeh Jan 17 '22 at 14:46
@Drawleeh Glad I (finally) could help. Sorry about the misunderstanding. I was sure that the row in Statistics should be deleted outside the loop... – FaneDuru Jan 17 '22 at 14:49

VBA Why does Delete Shift Up delete everything below it?

1 Answers1