selection end to end range in vba

[mrinal saha]

Hi Folks,


In the below code, I'm trying to get the rownumber of the cell containg "CONTROL NO". And than want to select the entire range from that cell to lr and lc. But selection is a problem statement for me now. need a line for that.


lr = ActiveSheet.UsedRange.Rows.Count
lc = ActiveSheet.UsedRange.Columns.Count

For i = 1 To 7
If Cells(i, 1).Value = "CONTROL NO" Then
rownum = Cells(i, 1).Row
End If
Next i


Cells(rownum, lc).Select

Thanks,
Mrinal

 

[VoG]

Try

Range(Cells(rownum, 1), Cells(lr, lc)).Select

 

[Weaver]

range(range("A1:A7").find("CONTROL NO"),cells(lr,lc)).select

perhaps

or replace the whole code with

Range(Range("A1:A7").Find("CONTROL NO"), Cells.SpecialCells(xlCellTypeLastCell)).Select

You know that you don't always need to 'select' cells in order to do things with them?