Search code examples
excelvbaintersect

Define range with last row & Intersect activecell and range

I'm new to VBA and am currently trying to define a range within a datasheet from column h11 to last data row in column h in order to run a macro, if the activecell is within that range.

VB tells me "Object Variable or With block variable not set" when defining rng1 (and probably rng2 as well).

I would like feedback on 2 things: What do I do to fix this error? How do I proceed with the intersect function, when I fix the first error?

I would really appreciate all help. I've been at it for a quite a while now and seems to be stuck. I've tried rng1 = range(range("H11") ,range("H" & Lastrow)). Please let me know if there is something I can do to learn faster or better - youtube videos or similar is very much appreciated.

Dim rng1 As Range
Dim rng2 As Range
Dim lastrow As Long

lastrow = Range("H" & Rows.Count).End(xlUp).Row

rng1 = Range("H11:H" & lastrow)
rng2 = Range("I11:I" & lastrow)

'Sort on Product
    If Intersect(activecell, rng1) = True Then```
Solution

  • These two lines should be:

    set rng1 = Range("H11:H" & lastrow)
set rng2 = Range("I11:I" & lastrow)

    With that, the intersect function should be:

    if not Intersect(activecell, rng1) is nothing then