Loop through cells and add to a range

How would I loop through cells B1 to J1 and add them to a range if they meet a certain criteria. For example.

Dim Range1 As Range
For i = 1 to 9
If Range("A1").Offset(1,i) meets a certain criteria Then
**Add that cell to Range1**
End If
Next i

I'm not sure how to approach the part of adding certain cells to Range1.

Thanks for the help!

Answers


Something like this using Union to glue together your range

  1. Please note that For each loops are quicker than a For i = 1 to x approach
  2. You may well be able to use SpecialCells to determine your new range instantly (e.g. any blanks, any errors, any formulae, etc)

    Sub Test()
      Dim rng1 As Range
      Dim rng2 As Range
      Dim c As Range
      Set rng1 = Range("B1:J1")
    
      For Each c In rng1
        ' Add cells to rng2 if they exceed 10
        If c.Value > 10 Then
            If Not rng2 Is Nothing Then
            ' Add the 2nd, 3rd, 4th etc cell to our new range, rng2
            ' this is the most common outcome so place it first in the IF test (faster coding)
                Set rng2 = Union(rng2, c)
            Else
            ' the first valid cell becomes rng2
                Set rng2 = c
            End If
        End If
      Next
    End Sub
    

Need Your Help

Storing Calllog details into a SQLite DB

android sqlite calllog

I am having some trouble with my application.

CSS: Change background on hover of <li>, color not filling the space

css hover html-lists

I'm having issues with getting this code player in progress to look proper: