Excel VBA Mutually Exclusive cells in a row dynamic 1

[JustATheory]

Greetings,

I've written code for mutually exclusive cells in a row, but they are static and only in one row. I need to have this work for all rows in a range, and would like it to be dynamic so that adding a column wouldn't be an issue. Has anyone done this? Any help is welcomed.

Thanks,
Andy

 

[SkipVought]

Hi,

Well, it sure would be helpful to see the code that you already have, that is, if you want help.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JustATheory]

Hi Skip,
Sorry about that, here you are. I'm working on a To Do or Status list and I'm each entry is for progress on items.

Thanks,
Andy

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Target.Address = "$A$2" Then [B2,C2].ClearContents
If Target.Address = "$B$2" Then [A2,C2].ClearContents
If Target.Address = "$C$2" Then [A2,B2].ClearContents

End Sub

 

[SkipVought]

How would your expanding range work?

How would you determine that a new column/row is to be included?

Would you ever define fewer columns/rows? If so, how would that happen?

Please explain the logic in detail, answering each question.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

This does not address the dynamic part, but this might give you some ideas for expanding the concept. My definition defines A2:C3...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, r As Range, rClear As Range
    
    Set rng = Range([A2], [C3]) 'the exclusive cell range, must be at least 3 columns
    
    If Target.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, rng) Is Nothing Then
        For Each r In Intersect(rng, Target.EntireRow)
            If Intersect(Target, r) Is Nothing Then
                If rClear Is Nothing Then
                    Set rClear = r
                Else
                    Set rClear = Union(rClear, r)
                End If
            End If
        Next
        rClear.ClearContents
    End If
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Here's a version that uses the UsedRange property to dynamically define the exclusive range. See how that workd for you...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, r As Range, rClear As Range
    
    With ActiveSheet.UsedRange
        Set rng = Range([A2], .Cells(.Rows.Count, .Columns.Count))
    End With
    
    If Target.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, rng) Is Nothing Then
        For Each r In Intersect(rng, Target.EntireRow)
            If Intersect(Target, r) Is Nothing Then
                If rClear Is Nothing Then
                    Set rClear = r
                Else
                    Set rClear = Union(rClear, r)
                End If
            End If
        Next
        rClear.ClearContents
    End If
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JustATheory]

I think I could define the range ([A2], [C1048576]), then when adding columns I would add them between A and C.

Answers to your questions:

How would your expanding range work? - Adding new columns between A and C. I was planning on having a pre-named horizontal range for all rows and then two vertical ranges one for column A and then C. That way I wouldn’t need to add rows, they would all inclusive. Then the issue be would adding columns on the fly between the vertical ranges.

How would you determine that a new column/row is to be included? - I’m building a template, so columns would be added based on the new project. All rows would be included, adding new columns based on new project.

Would you ever define fewer columns/rows? If so, how would that happen? - Same answer as above.


Thanks Skip you are always extremely helpful.

Thanks,
Andy

 

[SkipVought]

Adding new columns between A and C

Sorry, there is ONE column between A & C and there NEVER can be more than ONE.

However, you could do this. Lets suppose that [red]ROW 1[/red] is your headings, where you can define additional columns. The additional rows can be defined by UsedRange.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, r As Range, rClear As Range
    
    Set rng = Intersect(Range([A1], [A1].End(xlToRight)).EntireColumn, _
        Range([A2], UsedRange.Cells(UsedRange.Rows.Count, 1).EntireRow))

    If Target.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, rng) Is Nothing Then
        For Each r In Intersect(rng, Target.EntireRow)
            If Intersect(Target, r) Is Nothing Then
                If rClear Is Nothing Then
                    Set rClear = r
                Else
                    Set rClear = Union(rClear, r)
                End If
            End If
        Next
        rClear.ClearContents
    End If
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JustATheory]

Hey Skip,

This is perfect, the UsedRange is helpful in a number of projects. Thank you!

Andy

 

[Gavona]

WindanSea: Doesn't Skip's answer deserve recognition? At the bottom of Skip's post there is something to click on looking like this:
[purple]*[/purple][blue] Thank SkipVought
for this valuable post![/blue]

I know you have solved the issue but, for those other projects, where usedrange doesn't work for you then range(xx).CurrentRegion might.

Also if you hard code a range in your code then Excel will not cope if you insert columns or rows etc. Using named ranges can help solve this issue and make your code more robust. Maybe this is what you had in mind when you said

I think I could define the range ([A2], [C1048576]), then when adding columns I would add them between A and C.

To do this you create a named range in the spreadsheet then your code your code would need to refer to this as range("myRange").

To redefine your range to include the added rows or columns:
range("myRange").CurrentRegion.name="myRange"

Gavin

 

[JustATheory]

Yes, I usually define the range for the finished product, making it dynamic. In this case the defined named range would be the header, then you can also use the used range for the entire columns, that is my plan for this project.

Thanks,
Andy

 

[townie192]

Andy and Skip, this thread has been extremely helpful. I copied the code you provided into my spreadsheet and the entire row is blanked out with the exception of the cell that I entered data into. I want to retain the data in most of the cells(example: columns a through H) and force the user to enter something into the cells in one of three columns (example: I though J. Can you help?

 

[SkipVought]

townie192,

Post your question in a NEW THREAD. Please describe the structure of your sheet fully when you do and explain exactly what happens; cause and effect.

Do not post back in this thread.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]