Loop through cells and add to a range

Question

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!

score 30 · Accepted Answer · edited Jul 08 '13 at 23:07

30

Something like this using Union to glue together your range

Please note that For each loops are quicker than a For i = 1 to x approach

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

edited Jul 08 '13 at 23:07

DACrosby

11,116
3
39
51

answered Nov 30 '11 at 03:42

brettdj

54,857
16
114
177

are u sure "For each loops are quicker than a For i = 1 to x approach"? i thought otherwise https://stackoverflow.com/questions/365615/in-net-which-loop-runs-faster-for-or-foreach – curious Jul 24 '17 at 04:49
1

`For Each` is faster for a range, `For I` faster for looping through a variant array – brettdj Jul 25 '17 at 05:17
o ok. thanx for the clarification – curious Jul 26 '17 at 02:29
Shouldn't it be 'Next C' or doesn't it matter? – Gary Carlyle Cook Dec 09 '19 at 21:59

score 1 · Answer 2 · answered Apr 10 '15 at 13:16

I use this method in immediate mode when I don't want to add code to the sheet.

strX="": _
For Each cllX in Range( ActiveCell, Cells( Cells.SpecialCells(xlCellTypeLastCell ).Row, ActiveCell.Column) ): _
strX=strX & iif(cllX.text="","",iif(strX="","",",")& cllX.address): _
Next: _
Range(strX).Select

But while that is intuitive, it only works for up to 35 to 50 cells. After that, the VBA returns an error 1004.

Run-time error '1004':
Application-defined or object-defined error

It is more robust to use the Union function.

Set rngX=ActiveCell: _
For Each cllX in Range( ActiveCell, Cells( cells.SpecialCells(xlCellTypeLastCell ).Row, ActiveCell.Column) ): _
Set rngX=iif( cllX.text="", rngX, Union(rngX, cllX) ): _
Next: _
rngX.Select

It is so short and intuitive, I just throw it away after each use.

Loop through cells and add to a range

2 Answers2

Linked