VBA: How do I remove a cell from a range

[Jshugs]

I have a function that adds a cell to a range whenever an "a" is placed in that cell using Set myRange= Application.Union(myRange, Target) under Private Sub Worksheet_Change(ByVal Target As Range). If a value other than "a" is then placed in the same cell I want to remove that cell from myRange. It is fairly random which cells someone will put an "a" in. Does anyone know of a function, or any way to do this. Basically if there are 4 cells in myRange (A1, B4, C6, D8) I just want to remove the last cell and have myRange have 3 cells now (A1, B4, C6). I have a feeling I might need to explain this further, but hopefully someone understands what I am trying to do.

thanks!
-Jon

 

[tusharm]

See
Two new range functions: Union and Subtract
http://www.dailydoseofexcel.com/archives/2007/08/17/two-new-range-functions-union-and-subtract/

 

[PA HS Teacher]

How about the following?

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>   <SPAN style="color:#00007F">Dim</SPAN> TempRange <SPAN style="color:#00007F">As</SPAN> Range<br>   <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>   <SPAN style="color:#00007F">If</SPAN> Target.Cells.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>   <SPAN style="color:#00007F">If</SPAN> Target.Value = "a" <SPAN style="color:#00007F">Then</SPAN><br>      <SPAN style="color:#00007F">If</SPAN> myRange <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Set</SPAN> myRange = Target<br>      <SPAN style="color:#00007F">Set</SPAN> myRange = Application.Union(myRange, Target)<br>   <SPAN style="color:#00007F">Else</SPAN><br>      <SPAN style="color:#00007F">If</SPAN> myRange <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>      <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> myRange.Cells<br>         <SPAN style="color:#00007F">If</SPAN> TempRange <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> And c <> Target <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Set</SPAN> TempRange = c<br>         <SPAN style="color:#00007F">If</SPAN> c <> Target And <SPAN style="color:#00007F">Not</SPAN> TempRange <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Set</SPAN> TempRange = Application.Union(TempRange, c)<br>      <SPAN style="color:#00007F">Next</SPAN> c<br>      <SPAN style="color:#00007F">Set</SPAN> myRange = TempRange<br>   <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>   <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> myRange <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> MsgBox myRange.Address<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Jshugs]

Wow, thanks guys! Both responses were very helpful!

thanks again!

-Jon

 

[Jshugs]

I actually have one other question that is really bugging me. I have the public variable myRange (which contains all the cells with an "a"), but now I am trying to hide the comments in that range of cells when the user clicks on a new cell. I am using Private Sub Worksheet_SelectionChange(ByVal Target As Range), and my code almost works perfectly, except whenever I delete all the cells in myRange, I get "run-time error '424' object required" and I don't know how to fix it. I tried putting
if myRange is nothing then exit sub
at the top of the function, but for some reason that doesn't work. Any ideas?

thanks again!

-Jon

 

[Jshugs]

I cut down my code to just contain the problematic part.

Public myRange As Range
Sub Macro1()
Set myRange = Range("B1,C5,D6")
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rCell As Range
    If myRange Is Nothing Then Exit Sub
 
        For Each rCell In myRange.Cells
            If Not rCell.Comment Is Nothing Then
                If Not rCell.Comment.Text = "COMMENT: " Then
                    rCell.Comment.Visible = False
                End If
            End If
         Next
 
End Sub

If I delete all the cells in myRange then I get that error. Anyone know how to fix this?

thanks
-Jon

 

[tusharm]

What line do you get the error on?

 

[Jshugs]

For Each rCell In myRange

For some reason, myRange Is Nothing doesn't apply when you delete those cells. I don't understand it.

Oh, I've tried For Each rCell In myRange.Cells and For Each rCell In myRange. I don't really know the difference between the two; they both do the same thing as far as I know.

 

[tusharm]

Step through the code and see what myRange contains. If you don't know how put a breakpoint on the If myRange is nothing test line. If you don't know how, click on the left border (it should be a grey border) of that line. Or, put in a 'Stop' statement after the If test.

 

[Jshugs]

Everytime I delete one of the cells, the range removes that cell from the range and shifts the others over. When I remove all three it seems like there is nothing in the range. I am using MsgBox myRange.Address to check which cells are in the range. and when I hover over the source code MsgBox myRange.Address it says "myRange.Address=object required"