VBA - If Target Not Intersect Till Last Row

[hrayani]

Hi All,

I am using the range A4:A100 since I do not know how to change it to look from A4 till the last row.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)   
  
 If Not Intersect(Target, Range("[B][COLOR=#ff0000]A4:A100[/COLOR][/B]")) Is Nothing Then
   
 Call Macro1
   
End If
    
End Sub

If anyone friend can provide a solution.

Thanks in advance

Regards,

Humayun

 

[Joe4]

One way:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)   
  
 Dim lr as Long

 lr = Cells(Rows.Count, "A").End(xlUp).Row

 If Not Intersect(Target, Range("[B][COLOR=#ff0000]A4:A" & lr[/COLOR][/B])) Is Nothing Then
      Call Macro1
 End If
    
End Sub

 

[Fluff]

How about

 If Not Intersect(Target, Range("[B]A4:A[/B]"&Rows.Count)) Is Nothing Then

 

[hrayani]

Thanks Joe,

Working Just Perfect

 

[hrayani]

Thanks Fluff,

Tried your code too... Its looking beyond the last row.

 

[Joe4]

You are welcome.

 

[Fluff]

Thanks Fluff,

Tried your code too... Its looking beyond the last row.

Mine actually looks to the lastrow as specified, Joe's looks to the last used row

 

[Joe4]

Mine actually looks to the lastrow as specified, Joe's looks to the last used row

Just to clarify, that returns the last possible row on the entire sheet (row 1048576 on xlsx files).

As Fluff said, mine chooses the last used row in column A (so it stops there).

 

[hrayani]

Thanks.... Got it

I realised that in my first post I did not write that I want last used row.... instead I wrote last row.
I should have written last used row