excel offset range

[mhghg]

Hello guys,
This is my sheet, I try to define the table1 range, what I dont understand is FoundCell.Select returns 1 row only. How come my lRow after subtrating FoundCell.Row is 2, which is supposed to be 3 ?

With Sheet1
    Set FoundCell = .Cells.Find("ID").Offset(1, 0)
    FoundCell.Select
    lRow = .Cells(.Rows.Count, FoundCell.Column).End(xlUp).Row - FoundCell.Row
    Set Table1 = FoundCell.Resize(lRow, 2)
    Table1.Select
End With

 

[NARAYANK991]

Hi ,

Initially , you are finding the first row of data ; for this , you find the word ID , and then go down one row. Thus , in your case , the first row of data would be row 2.

Now , you find the last row of data ; this returns the value 4.

So , if the first row of data is row 2 , and the last row of data is row 4 , it means there is data in row 2 , row 3 and row 4.

Your code should be as follows :

With Sheet2
    Set FoundCell = .Cells.Find("ID").Offset(1, 0)
    numberofrows = .Cells(.Rows.Count, FoundCell.Column).End(xlUp).Row - FoundCell.Row + 1
    Set Table1 = FoundCell.Resize(numberofrows, 2)
    Table1.Select
End With

Narayan

 

[mhghg]

Ah I Got it Narayan. They used the absolute reference right !
Thanks Narayan. You are the gem to us!