Excel VBA - Dynamic Range to use with For Each Statement

0
votes

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.

2
excelvba
Note that .End(xlDown) can be unreliable; best go xlUp from the very bottom of the sheet, especially if the column might have empty rows. – Mathieu Guindon

2 Answers

0
votes

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
0
votes

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