Excel-VBA - How to identify Target range more than 1 cell is deleted in a Worksheet Change function

0 votes

I'm attempting to determine whether the worksheet change function will cause cells in the same rows in column X to also be deleted if a user deletes values from certain cells in column B.

IsEmpty(Target) returns true when I delete just one cell in column B, allowing me to erase the identical row cell in column X.

IsEmpty(Target) returns False when many cells in column B are selected and the delete button is pressed. Target is currently a range of several cells. Simply put, I am unable to determine whether a user simultaneously erased a range of numbers in column B. Any assistance would be greatly valued.

Below code works when one cell is deleted but not when a range of cells are deleted.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column <> 2 Then Exit Sub
    If Target.Columns.Count > 1 Then Exit Sub

    If IsEmpty(Target) Then

        Application.EnableEvents = False
        ActiveSheet.Range("X" & Target.Row & ":X" & Target.Row + Target.Rows.Count - 1).ClearContents
        Application.EnableEvents = True
    End If

End Sub

Can someone please help me with this?

Sep 22 in Others by Kithuzzz
• 20,660 points
513 views

1 answer to this question.

0 votes

You misunderstand the purpose of the function IsEmpty

I assume the cells without a value are what you're truly looking for (blank cells). The number of cells with values is displayed in the next line. If it equals 0, they are all left empty.

Target.SpecialCells(xlCellTypeConstants).Count

Yet, the above line of code will result in an error if all cells are empty. Hence, you will have to adjust your code as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 2 Then Exit Sub
If Target.Columns.Count > 1 Then Exit Sub

Dim bolIsEmpty As Boolean

On Error GoTo AllAreEmpty
bolIsEmpty = Target.SpecialCells(xlCellTypeConstants).Count = 0
On Error GoTo 0

If bolIsEmpty Then
    ' ... your code here ...
End If

Exit Sub

AllAreEmpty:
    bolIsEmpty = True
    Resume Next

End Sub
answered Sep 23 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer

What is a name function in JavaScript & how to define it?

A named function declares a name as ...READ MORE

answered Mar 7, 2019 in Others by Frankie
• 9,830 points
1,732 views
0 votes
1 answer

How to stick an embedded document in a specific cell of an excel

Solution Select the documents (you can use the ...READ MORE

answered Oct 18 in Others by narikkadan
• 37,660 points
34 views
0 votes
1 answer

Is there a function to unhide columns in excel through python

Excel file : df.to_excel('demofile.xlsx',index=False) import openpyxl py = openpyxl.load_workbook('demofile.xlsx') exlsheet = ...READ MORE

answered Oct 18 in Others by narikkadan
• 37,660 points
79 views
0 votes
1 answer

How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

answered Oct 24 in Others by narikkadan
• 37,660 points
41 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
452 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,222 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9 in Others by gaurav
• 22,040 points
69 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16 in Others by Edureka
• 13,640 points
208 views
0 votes
1 answer

Excel VBA : HOW TO PRINT THE TEXT IN A CELL (like Wrap Text)

Use a LineFeed character to create a ...READ MORE

answered Oct 27 in Others by narikkadan
• 37,660 points
79 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP