Reference material for If Not/Intersect/Nothing Then

[julhs]

Can someone point me in the right direction for a good source for this.

Many thanks

 

[RoryA]

There isn't really a source for that specifically that I am aware of as it's just a combination of things. Intersect either returns a Range (where the provided ranges overlap) or Nothing (if there is no overlap. Generally in the kind of code I assume you are talking about, you don't want to do anything if there is no overlap, so you need to check that the result of Intersect is not Nothing. You could do that like this:

If Intersect(range1, range2) Is Nothing then
 ' don't do anything
Else
  ' run your code here because there is an overlap
End If

but given that the first (True) part of the if usually doesn't involve any code, most people prefer to negate the test so you don't need an else:

If Not Intersect(range1, range2) Is Nothing then
  ' run your code here because there is an overlap
End If

 

[julhs]

Thanks Rory
I bumbled my way through a previous problem and came up with the solution below that achieved what I wanted:

If Not Intersect(Target, Range("ChemicalSuppliersList_Condensed")) Is Nothing Then
LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Call ReFormatRange
End If
'       Named Range "ChemicalSuppliersList_Condensed" = $A$8:$A$26
'       LastRow gave me last used row on the Sheet

To do something else I need to find the LastRow of NamedRange "ChemicalSuppliersList_Condensed" and NOT LastRow of the sheet.
If it makes any difference, this will all be part of a single Change Event procedure.
But thanks to utter brain fog I can’t figure out how to structure it!!

 

[Engberg]

Thanks Rory
I bumbled my way through a previous problem and came up with the solution below that achieved what I wanted:

If Not Intersect(Target, Range("ChemicalSuppliersList_Condensed")) Is Nothing Then
LastRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Call ReFormatRange
End If
'       Named Range "ChemicalSuppliersList_Condensed" = $A$8:$A$26
'       LastRow gave me last used row on the Sheet

To do something else I need to find the LastRow of NamedRange "ChemicalSuppliersList_Condensed" and NOT LastRow of the sheet.
If it makes any difference, this will all be part of a single Change Event procedure.
But thanks to utter brain fog I can’t figure out how to structure it!!

Replace your "LastRow =" with these, depending on what your need. And replace "TEST" with the name of your range of course.

    namedRangeCount = Range("TEST").Rows.Count() 'The number of rows in a named range [INTEGER]
    LastRow = Range("TEST").Rows(namedRangeCount).Row 'The absolute number (relative to sheet)  of the last row in the named range [INTEGER]
    LastRowAsRange = Rows(LastRow).Cells 'The entire last row of the named range as a range [RANGE]

 

[julhs]

Thank you both for the advice.
Rory; example you gave made me realise I could put multiple target ranges into one Intersect argument that all Call the same Sub.
Egburg; sorry I’ve not done you justice by not testing it yet, needed to finish off other things, when I’ve finished I will give feedback.
Many thanks
Julhs