Reading Entire Range

[Peter]

I have multiple workbooks with one sheet in them named Sheet1.
There is only one column filled with data, 'A'
The rows in column A are filled with text data.
I never know how many cells are in column 'A'.

How do I create a loop that goes through the entire range?

Here is my code that doesn't loop:

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet1 As Excel._Worksheet
Dim mytext1 As String


xlApp = CreateObject("Excel.Application")
xlBook = xlApp.Workbooks.Open("C:\MyXLS.xls")
xlSheet1 = xlBook.Worksheets(1)

mytext1 = xlssheet1.range("A1").value 'I need all the values in A




xlBook.Close()

 

[Tom Ogilvy]

Dim rng as Excel.Range
Dim cell as Excel.Range


set rng = xlSheet1.Range(xlSheet1.Range("A1"), _
xlSheet1.Range("A1").End(xldown))
for each cell in rng

Next