Excel VBA - Dynamic Range to use with For Each Statement

Question

I'm trying to dynamically define a range in row like ctrl+down or ctrl+shift+down (to next blank cell) to be used with "For Each itm In rng" statement.

Originally I had it static like this set rng = Range("A4:A10")
So I tried to change it to something like this

Dim rng As Range

Set rng = Range("A4").CurrentRegion.Rows.Count    

    For Each itm In rng
        ...        
    Next itm

I also tried something like this

Set StartCell = Range("A4")
rng = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row

But the code doesn't seems to work with "For Each itm In rng" statement
Any help is very much appreciated.

Note that `.End(xlDown)` [can be unreliable](https://stackoverflow.com/a/11169920/1188513); best go `xlUp` *from the very bottom of the sheet*, especially if the column *might* have empty rows. — Mathieu Guindon, Jun 05 '19 at 19:41

Damian · Answer 1 · 2019-06-05T19:08:51.827

0

Try this if it helps:

Option Explicit
Sub Test()

    Dim LastRow As Long 'to find the last row on your range
    Dim MyRange As Range 'to reference the whole range
    Dim C As Range 'to loop through your range

    With ThisWorkbook.Sheets("MySheet") 'change MySheet for your sheet name
        LastRow = .Cells(4, 1).End(xlDown).Row 'last row, how? You go down to the last row and then ctrl + up
        Set MyRange = .Range("A4:A" & LastRow) 'dynamic range
        For Each C In MyRange
            'your code
        Next C
    End With

End Sub

edited Jun 05 '19 at 19:08

answered Jun 05 '19 at 19:00

Damian

5,152
1
10
21

There are other values and tables in between, so I can't go down to the last row in A and ctrl up. But I can start from A4 and ctrl down to next blank cell and that's the range that I'm trying to define – ggmkp Jun 05 '19 at 19:06
@GimGanDi try it now – Damian Jun 05 '19 at 19:09
`LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row` would be better. – FAB Jun 05 '19 at 19:18

score 0 · Accepted Answer · answered Jun 05 '19 at 19:08

0

You can use .xlDown, it's the equivalent of pressing ctrl+Shift+down.

Dim rng As Range
Dim lastRow As Long

lastRow = Range("A4").End(xlDown).Row
Set rng = Range("A4:A" & lastRow)

For Each itm In rng
    'do something
Next itm

answered Jun 05 '19 at 19:08

SendETHToThisAddress

2,756
7
29
54

`LastRow = Range("A", Rows.Count).End(xlUp).Row` would be better. `xlDown` is quite unpredictable. Also declaring/setting the sheets variable is recommended. – FAB Jun 05 '19 at 19:44
If I want the range to start from A4 then I would put simply replace `= Range("A",..` with `"A4",..` ? – ggmkp Jun 05 '19 at 19:58
I get an error using `lastrow = Range("A4", Rows.Count).End(xlUp).Row` `Set rng1 = Range("A4:A" & lastrow)` – ggmkp Jun 05 '19 at 20:05
@GimGanDi yes, if you want the range to start at A4 then you would enter A4 as shown. Range objects use the format of ("A4:B6"), where A4 is the first cell on the upper left of the range and B6 would be the last cell in the lower right. – SendETHToThisAddress Jun 06 '19 at 18:30

Excel VBA - Dynamic Range to use with For Each Statement

2 Answers2