range

[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

[Ecniv]

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

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

To get the range (total) into a string

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

Vince