Trying to loop non-contiguous rows in a range

Question

I am trying to write something that will loop through a range of non-contiguous rows in a table, and change column data in each row ... for example, ClearContents. The range will be dynamic, and the rows I want to loop through will all be "Selected."

I tried the following, but it stopped after the first row. I am pretty sure the problem is that the next row is non-contiguous to the first row:

 For Each b In a.Rows
 mainTasks.DataBodyRange(Range("mainTasks[Status]").Column).ClearContents
 Next b

Then I had the bright idea to write something that works only the "selected" column cells. I tried using If ... .Value = Selected and that didn't work.

Am I trying to do something that Excel 2016 VBA can't do? That is, loop through non-contiguous rows in a range? I've been researching and tried several other things that don't work. Can you tell me if I am going down the wrong rabbit hole?

Answer 1

I tried to simplfy your code in the comments for testing purpose, but the following code should help,

Dim x As Integer
Dim myrange As Range
For x = 1 To 30
   If (mainTasks.Cells(x, 2) = "completed" Or mainTasks.Cells(x, 2) = "Dismissed") And mainTasks.Cells(x, 3) <> "" Then
      If myrange Is Nothing Then
         Set myrange = mainTasks.Cells(x, 2)
      Else
         Set myrange = Union(myrange, mainTasks.Cells(x, 2))
      End If
    End If
Next x
myrange.ClearContents

I tried it out and it works Ok given the way I set it up. Hope it helps!

You will also have to add the code to copy the data, I am clearing the contents in this one, given that was your original question

Answer 2

You don't have any variables in your loop.

It will just the clear the one column repeatedly in the loop. You need to somehow reference b in the loop to have a different outcome in each loop.

Answer 3

This works too (i think)

Dim selectedRange As Range

Set selectedRange = Application.Selection

For Each Row In selectedRange.Rows
    Debug.Print Row.Address
Next Row