View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
davegb davegb is offline
external usenet poster
 
Posts: 573
Default Different way of selecting range

The following code works fine to determine if the currently selected
cell is in the desired range:

Sub SelectCellinRange()
Dim rStart, rEnd As Range
Dim lRow, lCol As Long
Dim lBRow As Long


Set rStart = Range("B4")

lRow = rStart.End(xlDown).Offset(-1, 0).Row
lCol = Cells(lRow, Columns.Count).End(xlToLeft).Column

Set rEnd = Range(rStart, Cells(lRow, lCol))

If Not IsCellInRange(ActiveCell, rEnd) = True Then
MsgBox "Please select a county!"
End
End If
End Sub

The problem is, that in one of the sheets there are blanks in the
bottom rows, and the full range that the selected cell can be in is not
identified. I want to count the columns in row 4 rather than the last
row in the range. How do I do that?
When I try to put in Row("4:4") in place of lRow in the lCol line, XL
doesn't know what a row is. And you can't have rows in Activesheets. So
I can't figure out how to get it to figure out what a row is in this
situation. I think I could if I defined what worksheet I'm in and
passed that name from the macro calling this one into this one, but
that seems uneccessarily complicated.
Thanks!