Vba that deals with noncontiguous range

[twl2009]

Hi,

I have the simple bit of code below, that add or removes a percentage of cells value. I need to apply this to quite a large number of cells that wont be a contiguous range, is there an easy way of doing this with writing the same line many times?

Sub SalesSeasons()
       Worksheets("SalesForecast").Range("H17") = Range ("H17").Value*Range("X6").Value
End Sub

 

[JackDanIce]

Just Set the range to the new cells, e.g.

Dim rng as Range

Set rng = Range("A1:B1")
rng.Select
Set rng = rng.Offset(0,2)
rng.Select
Set rng = Range("A3:B3")
rng.Select

Set rng = Nothing

 

[My Aswer Is This]

Lets assume your named range is named "My_Range"
Then your script would look like this:

Sub Multiply_Range()
Dim c As Range
    For Each c In Range("My_Range")
    If c.Value <> "" Then c.Value = c.Value * Range("X6")
    Next
End Sub

 

[Peter_SSs]

once you have set a named range how do you change the cells assigned to it?

There would be several ways including a macro or using the Name Manager in the 'Formulas' ribbon tab to edit the range or Delete it and start again. It would depend to some extent on how many cells are in the range, whether you are just adding more cells to it or adding and deleting cells, how often you would be changing the range etc.

Hard to suggest a 'best' method without knowing quite a bit more about just what you have, what you are trying to achieve and how you would be deciding which cells should be in the range at any particular time.

 

[DEksel]

There would be several ways including a macro or using the Name Manager in the 'Formulas' ribbon tab to edit the range or Delete it and start again. It would depend to some extent on how many cells are in the range, whether you are just adding more cells to it or adding and deleting cells, how often you would be changing the range etc.

Hard to suggest a 'best' method without knowing quite a bit more about just what you have, what you are trying to achieve and how you would be deciding which cells should be in the range at any particular time.

Only 2 years passed by...
It is easy to refer each cell in the range but how to refer to the second cell in that range (or 4th: A20 or A39). For example, union range contains A1, A20, A22, A39, A41, A44, A45... If I want to get A20, I can't call it simple as myRange.cells(2). A2 will be response. Sorry for this kind of question but couldn't find the answer.

 

[Peter_SSs]

.. how to refer to the second cell in that range (or 4th: A20 or A39). For example, union range contains A1, A20, A22, A39, A41, A44, A45... If I want to get A20, I can't call it simple as myRange.cells(2). A2 will be response. Sorry for this kind of question but couldn't find the answer.

The method would depend on the individual circumstances. In your example, where all cells are listed individually, then to extract a particular one, you could use something like this.

Sub ReferToCell()
  Dim rng As Range
  Dim WhichCell As Long
  
  Set rng = Range("A1, A20, A22, A39, A41, A44, A45")
  WhichCell = 2
  MsgBox Split(rng.Address, ",")(WhichCell - 1)
End Sub

If some of the cells are grouped, but still in the same column (eg A1, A20:A22, A39, A41, A44:A45) then a different method

Or come from different columns (eg G15, B2:D7, C3:C5, C1) then it could be trickier and you may have to define exactly what you mean by the "second" cell in a range like that.

etc

 

[DEksel]

Ok. This is clear. Array approach. Or "work around". I thought that there are some simple way similar to cells call as rng.Cells.SpecialCells(xlCellOrderSecondCell) )

 

[Peter_SSs]

I thought that there are some simple way similar to cells call as rng.Cells.SpecialCells(xlCellOrderSecondCell) )

No, and if you think about it if the range was
A1:B2 would the second cell be A2 or B1?

Or the range A10, D12, E5?

 

[DEksel]

No, and if you think about it if the range was
A1:B2 would the second cell be A2 or B1?

Or the range A10, D12, E5?

If we continue similarity with arrays (that is absolutely correct idea), my question was about single dimensional array. You asked about multi-dimensional arrays. By default, in m-d array A1:B2, second cell is A2 as long as B1 is not the second cell... But I see your point. Reason why they didn't make more simple way to call the 2nd cell in non consecutive range ie. it is simple impossible. Arrays do have items order while ranges not.

My first question (re: A1, A20, A22, A39, A41, A44, A45...) was about very concrete problem. I need to transpose some cells-data from dirty textual one-column group of data to proper tabular data. Each group has its own start which is never at the same place. Distance between them vary of course. So, I created array of group starts and loop through each of start and grab data between 2 starts. Then, I needed to start from the second start of data. In array, you just do shift or kill the first item... go back from total starts qnty and end with 1... many ways. Here, in range I didn't try your solution because I've solved it before answer on some wild way.

 

[Peter_SSs]

My first question (re: A1, A20, A22, A39, A41, A44, A45...) was about very concrete problem.

Fair enough, & I gave you a way in post 15 to refer to the second cell for a non-contiguous range written in the form you gave.

If we are looking at your specific example type (where every cell in the vertical range is identified separately) then you can refer to the nth cell directly by using the Areas property like this

Sub ReferToCell()
  Dim rng As Range
  Dim WhichCell As Long
  
  Set rng = Range("A1, A20, A22, A39, A41, A44, A45")
  WhichCell = 2
  rng.Areas(WhichCell).ClearContents
End Sub

In array, you just ... kill the first item...

You can do that with a range specified like yours too

Sub RemoveFirstCell()
  Dim rng As Range
  
  Set rng = Range("A1, A20, A22, A39, A41, A44, A45")
  Debug.Print "Range address is " & rng.Address(0, 0)
  Do Until rng.Cells.Count = 1
    Set rng = Range(Split(rng.Address, ",", 2)(1))
    Debug.Print "Range address is " & rng.Address(0, 0)
  Loop
End Sub

 

[DEksel]

You can do that with a range specified like yours too

Sub RemoveFirstCell()
  Dim rng As Range
  
  Set rng = Range("A1, A20, A22, A39, A41, A44, A45")
  Debug.Print "Range address is " & rng.Address(0, 0)
  Do Until rng.Cells.Count = 1
    Set rng = Range(Split(rng.Address, ",", 2)(1))
    Debug.Print "Range address is " & rng.Address(0, 0)
  Loop
End Sub

Yep, this line did the trick.

Set newRng = Range(Split(sourceRng.Address(0, 0), ",", 2)(1))

This limit (",2") is very interesting! I never used before. Also this "Address(0, 0)" is nice one My codes are full of Address(false, false)...

Thank you very much Peter. Sure I will use this way with pleasure!