Loop also taking blank cells when there is just one row

[fari1]

Hi,
I've a loop code, which runs through a column and connects every URL in that column to the web and brings in data. it works perfect, when there'r two or more than two cells of URLs, but when there's just one cell of URL, then, it starts considering the blank cell as well into the loop and obviously gives error. can somebody amend it to make it work even when there is single cell of data

Sub loopB()
Dim cel As Range
Dim mySheet As Worksheet
Set mySheet = Sheets("sheet1")
For Each cel In Sheets("URLs").Range("A2:A" & Sheets("URLs").Range("A2").End(xlDown).Row)
mySheet.Range("A1") = cel.Value
Next cel
End Sub

 

[squidgeny]

This is because End(xlDown) skips to the bottom when its starting point is the first and only entry in the list.

Try starting it from Range("A1") instead.

 

[fari1]

still it dun work, can u pl give me the code, that may skip blank cells when it moves down

 

[squidgeny]

Is the list unbroken or does it have random gaps in it? If it has gaps, you'll need a whole lot more code.

If the list is unbroken then my solution should work, as long as A1 something in it (e.g. a column header)

 

[fari1]

the list is not broken, it has no gaps,and also in my other loop the data is starting from A1, and still there it is giving error

 

[squidgeny]

Is this what you tried?

Sub loopB()

Dim cel As Range
Dim mySheet As Worksheet

Set mySheet = Sheets("sheet1")
For Each cel In Sheets("URLs").Range("A2:A" & Sheets("URLs").Range("A1").End(xlDown).Row)
    mySheet.Range("A1") = cel.Value
Next cel

End Sub

When you get an error, what does the error say? What part of the code gets highlighted by the error?

 

[fari1]

how can i start the next string with A1, while earlier in the code it has A2. it didn't work

 

[squidgeny]

Because EndDown will return A2, unless you have no data in the list at all. Can you upload your spreadsheet?

 

[fari1]

here is got the code, problem resolved, thanks anyways

Sub loopB()
Dim cel As Range
Dim mySheet As Worksheet
Set mySheet = Sheets("sheet1")
For Each cel In Sheets("URLs").Range("A2:A" & Sheets("URLs").Range("A2").End(xlDown).Row)
mySheet.Range("A1") = cel.Value
Next cel
End Sub