ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Different way of selecting range (https://www.excelbanter.com/excel-programming/337409-different-way-selecting-range.html)

davegb

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!


davegb

Different way of selecting range
 
Sorry, meant to take out the Dim lBRow as long line before posting.
It's something I tried to get this to work. Ignore it.


davegb

Different way of selecting range
 
Thanks Tom! Boy do I feel dumb! Making it more complicated as usual!



All times are GMT +1. The time now is 11:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com