Reference Variant Array to Column

Question

I have code that prevents changes to more than one cell at once. It will however allow for more than one cell to be deleted at a time. Below is the code that I am using and it works well.

Dim vClear As Variant
Dim vData As Variant

'This prevents more than one cell from being changed at once.
'If more than one cell is changed then validation checks will not work.
If Target.Cells.Count > 1 Then
    vData = Target.Formula
    For Each vClear In vData
        If vClear <> "" Then 'If data is only deleted then more than one cell can be changed.
            MsgBox "Change only one cell at a time", , "Too Many Changes!"
                Application.Undo
                Exit For
        End If
    Next
End If

What I am trying to add to it is when data is deleted I want it to check which columns data is being deleted from. If any of the columns meet the requirement, then I need the data in the equivalent row in another column to be deleted as well.

Here is an example of what I am trying to do. There are 2 columns that I need checked, they are G & H. If the data is deleted from either of these 2 columns then I want column I to be deleted as well. Let’s say I select the range of D5:G10 and delete the contents from it. Since column G is one of the requirements I would want I5:I10 deleted as well. If I was to delete D5:F10 then it would not delete anything in column I since neither columns G or H were selected.

Below is an example code of what I am trying to do. I know it is impossible for my code below to work, this is just a brief summary of what I am trying to do, I can’t figure out how to get the variant to also check the column as well. Please let me know if someone knows how to do this.

Dim vClear As Variant
Dim vData As Variant

'This prevents more than one cell from being changed at once.
'If more than one cell is changed then validation checks will not work.
If Target.Cells.Count > 1 Then
    vData = Target.Formula
    For Each vClear In vData
        If vClear <> "" Then 'If data is only deleted then more than one cell can be changed.
            MsgBox "Change only one cell at a time", , "Too Many Changes!"
                Application.Undo
                Exit For
        Else
            If vClear = "" Then
                If vClear.Column = 7 Or vClear.Column = 8 Then
                    ActiveSheet.Cells(vClear.Row, 9) = ""
                End If
            End If
        End If
    Next
End If

Jon Crowell · Accepted Answer · 2013-06-27 04:36:39Z

1

I've modified your code to determine if either columns G or H are in the Target. If so, the corresponding rows in column I are also cleared. I also removed an unnecessary If test in the Else portion of the For loop.

Dim vClear As Variant
Dim vData As Variant
Dim firstRow As Long
Dim lastRow As Long

'This prevents more than one cell from being changed at once.
'If more than one cell is changed then validation checks will not work.
If Target.Cells.Count > 1 Then
    vData = Target.Formula
    For Each vClear In vData
        If vClear <> "" Then 'If data is only deleted then more than one cell can be changed.
            MsgBox "Change only one cell at a time", , "Too Many Changes!"
                Application.Undo
                Exit For
        Else
            ' if the target includes columns G or H, we also clear column i
            If Not Intersect(Target, Columns("G:H")) Is Nothing Then
                ' get the first row in the target range
                firstRow = Target.Rows(1).Row
                ' get the last row in the target range
                lastRow = firstRow + Target.Rows.Count - 1
                ' clear contents of corresponding rows in column i
                ActiveSheet.Range(Cells(firstRow, 9), Cells(lastRow, 9)).ClearContents
            End If
        End If
    Next
End If

edited Jun 27, 2013 at 4:36

answered Jun 27, 2013 at 4:27

Jon Crowell

22.4k15 gold badges94 silver badges113 bronze badges

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

1 Comment

Chris Over a year ago

Excellent, thank you. Now I know Intersect, never heard of it before. Excellent remark statements in the code. Thanks for your help it works great.

Collectives™ on Stack Overflow

Reference Variant Array to Column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related