range

Hi im trying to automate the range of a worksheet due to the fact that a number of worksheets of different ranges will be fed in to the software im making, ive got the following code below but everytime i try to run i get an error stating that the range of the worksheet failed...does anyone have any ideas on how to fix this please? any help appreciated, thanks :)

Dim RowCount As Long

Set oWorkbookExisting = oExcelExisting.Workbooks.Open(fName)
Set oWorksheetExisting = oWorkbookExisting.ActiveSheet

'reads total number of rows in excel file
RowCount = oWorksheetExisting.Cells(Rows.Count, "A").End (xlUp).Row

'for loop that generates a range problem
For Each Cell In oWorksheetExisting.Range(Cells(2, 1), Cells(RowCount, 11))

Next Cell

Quote:

---

Originally posted by fizz_uk83
Hi im trying to automate the range of a worksheet due to the fact that a number of worksheets of different ranges will be fed in to the software im making, ive got the following code below but everytime i try to run i get an error stating that the range of the worksheet failed...does anyone have any ideas on how to fix this please? any help appreciated, thanks :)

Dim RowCount As Long

Set oWorkbookExisting = oExcelExisting.Workbooks.Open(fName)
Set oWorksheetExisting = oWorkbookExisting.ActiveSheet

'reads total number of rows in excel file
RowCount = oWorksheetExisting.Cells(Rows.Count, "A").End (xlUp).Row

'for loop that generates a range problem
For Each Cell In oWorksheetExisting.Range(Cells(2, 1), Cells(RowCount, 11))

Next Cell

---

This assumes that the first column has no breaks in the list at all

VB Code:

RowCount = oWorksheetExisting.Cells(1,1).End (xldown).Row

To get the range (total) into a string

VB Code:

strRange = oWorksheetExisting.range(oWorksheetExisting.cells(2,1),oWorksheetExisting.cells(RowCount,1).address false,false

Vince