0

I have a range rng, let's say it covers A1:C5. Now I want to modify the range, in this example taking out all cells in column B (meaning B1:B5). In the code below I simply defined it manually, but is there some way to tell VBA to use rng as reference and "subtract" the cells I don't want?

Sub Test()
Dim rng As Range
Dim rngMod As Range

    Set rng = ActiveSheet.Range("A1:C5")
    Set rngMod = ActiveSheet.Range("A1:A5, C1:C5") '<-- can I somehow define this range by telling VBA to take out column B out of rng?
End Sub

For context: I have conditional formatting rules and would like the user to be able to adjust the ranges some rules are applied to (by selecting the cells he doesn't want to be formatted and clicking a button). My thinking is I'd have to copy the ranges (or their addresses) somwhere onto the worksheet and then I can go from there, but for this to work I need to be able to modify the range somehow.

Alex
  • 515
  • 5
  • 19
  • 1
    It can be done in a loop, but **based on what condition**? Do you really want to remove the column B:B cells of the range like a purpose, or only to see how it is possible? Basically, you must build a new range (`NewRange`) creating a `Union` of the cells which must remain in the new range. For Conditional Formatting you can use `NewRange.Address`... – FaneDuru Feb 20 '20 at 09:46
  • @FaneDuru The condition will be which cells are selected when the user clicks a button. After that, I will have to check what already defined ranges the selection affects (I assume this will be possible with `Intersect` or the like). – Alex Feb 20 '20 at 12:05
  • I really do not understand which are your expectations from our side. Your explanation does not clarify anything. Do you expect words like pieces of advice? I was thinking to create a piece of code able to solve your problem, but if I do not understand the necessary **real** condition, I cannot do anything... You need to build a second range which must exclude some cells, but **only according to some conditions**. Can you better describe which this/those condition(s) must be? Even a fake one... In such a case, I can try a piece of code, at least, to guide you in the right direction. – FaneDuru Feb 20 '20 at 12:37
  • OK. I will try to not care too much about your real need. I will post a piece of code explaining the principle to be followed. It will do exactly what you settled about your need... – FaneDuru Feb 20 '20 at 12:45
  • 1
    I prepared the code, but it looks, it is not possible, anymore... Your question has been quoted like a duplicate. – FaneDuru Feb 20 '20 at 12:46
  • @FaneDuru Sorry for not providing enough detail, I thought the condition wasn't really relevant and you could just remove a cell or cells, stored in a range object, from another range object. Thanks for writing something - I'm not sure if you can get this to me somehow, this site doesn't have private messaging, right? – Alex Feb 20 '20 at 13:12
  • It doesn't, unfortunately... I cannot poste the code here, because you will not understand anything. I am thinking, maybe you rephrase your question, adding a little more code. For instance, a loop `For Each cell in rng ... Next` and asking for a sample solution to obtain the output from code. After that, if you tag my name (@FaneDuru) here, I will post the code before somebody else (maybe) will not be pleased by the question quality... – FaneDuru Feb 20 '20 at 13:20

0 Answers0