CODEWITHSUNDEEP
excelvba
MITHU
MITHU

Reputation: 164

Unable to print the value of cells upto the last row

I'm trying to print the content of column A. There are 10 rows in there, so when I try like the following I get the results accordingly:

Sub PrintUptoLastRow()
    Dim oCel As Range

    For Each oCel In Range("A1", "A10")
        Debug.Print oCel
    Next oCel
End Sub

I can get the same results without hardcoding the last row:

Sub PrintUptoLastRow()
    Dim oCel As Range

    For Each oCel In Range("A1", Range("A" & Rows.Count).End(xlUp))
        Debug.Print oCel
    Next oCel
End Sub

The above two methods are identical as long as the last row is A10.

However, I can also get the results using:

Sub PrintUptoLastRow()
    Dim oCel As Range

    For Each oCel In Range("A1:A5")
        Debug.Print oCel
    Next oCel
End Sub

But things go wrong when I try to mimick the above using last row:

Sub PrintUptoLastRow()
    Dim oCel As Range

    For Each oCel In Range("A1" & ":" & Range("A" & Rows.Count).End(xlUp))
       Debug.Print oCel
    Next oCel
End Sub

It throws the following error:

Run-time error `1004`
Method `Range` of object`_Global` failed

How can I make the last piece of code work the way I tried?

Upvotes: 0

Views: 34

Answers (1)

Storax
Storax

Reputation: 12207

Your forgot an A and row:

For Each oCel In Range("A1" & ":A" & Range("A" & Rows.Count).End(xlUp).Row)

or you use the address property 

For Each oCel In Range("A1" & ":" & Range("A" & Rows.Count).End(xlUp).Address)

But be aware that you always refer to the ActiveSheet.

Upvotes: 1

Related Questions