Inactive sheet not updating

[JulianvO]

Greetings

I have 11 sheets in the workbook. The 2 sheets that I am experiencing problems with are Sheet1 and Sheet2.
Sheet2 contains a range of data - as per screenshot

I delete Information on Sheet1, which in turn deletes the corresponding data on Sheet2. As can be seen in the above screenshot. The data in Sheet2 is a conventional range, as opposed to a Table. A Table creates errors in other code. I need to delete the empty row in in the range, otherwise, new data added will be at the next row in the range. With multiple additions and deletions of data, the range will look like Swiss cheese. This also makes the display in a list box, on a user form, look unsightly.

Below is a macro that runs correctly. The problem that I am experiencing is that for the Macro to run, the sheet must be activated. Currently the macro is running from Sheet2

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

RemoveEmptyCells

End Sub

Help needed:

How can I run the macro without activating the sheet? Is it possible to have a macro that will automatically open (active) and then close the sheet, without the user noticing this?

Ideally, I would like to have Sheet2 hidden, if possible.

Macro:

Option Explicit

Sub RemoveEmptyCells()

With Sheet2

Application.ScreenUpdating = False

Dim LastCell As Range

Set LastCell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)

With Range("B4:J300").SpecialCells(xlCellTypeBlanks).Delete(xlShiftUp)
End With

End With

Application.ScreenUpdating = True

End Sub

Thanking you in advance for your assistance

Julian

 

[kevin9999]

How does deleting data on sheet1 delete data on sheet2?

 

[JulianvO]

How does deleting data on sheet1 delete data on sheet2?

Hi Kevin9999

Rub from a macro

Sub Booking_Delete()
If Sheet1.Range("B8").Value = Empty Then
MsgBox "Please select a booking to delete"
Exit Sub
End If

If MsgBox("Are you sure you want to delete this booking?", vbYesNo, "Delete Booking") = vbNo Then Exit Sub
BkgRow = Sheet1.Range("B8").Value
Sheet2.Range("B" & BkgRow & ":J" & BkgRow).ClearContents
Booking_New
Schedule_Refresh


End Sub

I hope this helps.

 

[kevin9999]

Thank you
I think you're just missing a dot in your existing code (before Range)

With .Range("B4:J300").SpecialCells(xlCellTypeBlanks).Delete(xlShiftUp)

 

[Alex Blakenburg]

Adding to @kevin9999's suggestion and assuming
1) The code below in the Sheet2 code module
2) The current code works

Then perhaps:

Sub RemoveEmptyCells()
    Dim LastCell As Range
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    With Me
        Set LastCell = .Cells.SpecialCells(xlCellTypeLastCell)
        .Range("B4:J300").SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp)
    End With
    
    Application.EnableEvents = True            ' <--- Check to ensure that this does not prematurely set this back to True since you are calling it via 2 change events
    Application.ScreenUpdating = True

End Sub

 

[JulianvO]

Adding to @kevin9999's suggestion and assuming
1) The code below in the Sheet2 code module
2) The current code works

Then perhaps:

Sub RemoveEmptyCells()
    Dim LastCell As Range
    Application.ScreenUpdating = False
    Application.EnableEvents = False

    With Me
        Set LastCell = .Cells.SpecialCells(xlCellTypeLastCell)
        .Range("B4:J300").SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp)
    End With
   
    Application.EnableEvents = True            ' <--- Check to ensure that this does not prematurely set this back to True since you are calling it via 2 change events
    Application.ScreenUpdating = True

End Sub

Hi Kevin9999 and Alec

Thanks. It is always the small things that trip one up. An extra pair of eyes are always needed.