check if cell in named range

[xzaqus]

Hello,
I have two sheets sheetA and sheetB with two names ranges rngA and rngB respectively.

rngA in sheetA has one column and contains cell addresses (like, $F$6, $H$2, $G$3, ...)
rngB in sheetB is say $F$3:$H$9

I need to delete cell addresses in rngA that are not in rngB. For example, $H$2 is outside $F$3:$H$9, so the entire row containing value $H$2 in sheetA should be deleted.

Any help will be appreciated. I have tried intersect (could not get it to work), double loop (not very effective). Thank you for your help.

 

[louisH]

Hi.

Can you try this ?

Sub test()

    Dim rngA, rngB, c As Range
    Dim wsA As Worksheet
    Set wsA = Worksheets("SheetA")
    Set rngA = Worksheets("SheetA").Range("rngA")
    Set rngB = Worksheets("SheetB").Range("rngB")
    
    For Each c In rngA
        
        If Intersect(c, wsA.Range(rngB.Address)) Is Nothing Then
            c.EntireRow.Delete
        End If
        
    Next c

End Sub

 

[louisH]

Hi.

Can you try this ?

Actually, after experimenting & trying it a little bit i noticed it wouldn't work as expected (for example the named range would refer to Nothing since you delete its reference).

This code should solve the couple problems i noticed :

Sub test()

    Dim rngA, rngB, c As Range
    Dim wsA As Worksheet
    Dim strBuffer, refTo As String
        
    Set wsA = Worksheets("SheetA")
    Set rngA = Worksheets("SheetA").Range("rngA")
    Set rngB = Worksheets("SheetB").Range("rngB")
    
    refTo = Names("rngA").RefersToR1C1
    
    For Each c In rngA
        
        If Intersect(c, wsA.Range(rngB.Address)) Is Nothing Then
            strBuffer = strBuffer & "," & c.Address
        End If
        
    Next c
    
    strBuffer = Right(strBuffer, Len(strBuffer) - 1)
    
wsA.Range(strBuffer).Rows.EntireRow.ClearContents 'If you want to clear the content of the row
    'wsA.Range(strBuffer).Rows.EntireRow.Delete 'If you want to delete the row (contents will move)
    
    Names("rngA").Delete
    Names.Add Name:="rngA", RefersToR1C1:=refTo

End Sub

 

[xzaqus]

Thanks for the help. It did not work as expected.

This deletes entire rngA/ sheetA data.

Also, if rngA is added back from refTo, the new range will have blank rows/cells at the end (equal to the number of rows deleted).

What is strBuffer for?

 

[Rick Rothstein]

I think this may do what you want. It changes the RefersTo property of the rngA Defined Name to refer to those cells in rngA that are also in the address range of rngB.

[table="width: 500"]
[tr]
	[td]Sub TrimRange()
  With Range("rngA").Parent
    Names("rngA").RefersTo = "='" & .Name & "'!" & Intersect(Range("rngA"), .Range(Range("rngB").Address)).Address
  End With
End Sub[/td]
[/tr]
[/table]

 

[louisH]

This deletes entire rngA/ sheetA data.

My bad, I minsinterpreted this sentence in your initial post :

so the entire row containing value $H$2 in sheetA should be deleted.

You actually wanted to change the reference of rngA, not the values of the cells.
Sorry for the miscomprehension.

What is strBuffer for?

It's a technique i like to use to select every cell with a particular criteria in 1 line of code.
strBuffer will contain the address of every cell that needs to be deleted, then with Range(strBuffer) you can operate on every cell at the same time.

 

[xzaqus]

It is still not working. The problem is in the intersect as it captures all cells/rows and then deletes all. I output the strBuffer and it is a list of all the rows in rngA. Is it matching c with the value in rngB? rngA has cell addresses as value, which needs to be matched with the addresses of cells in rngB.

I think your first solution should also work as I have a dynamic named range. it should get updated if a row is deleted.

Hi.

Can you try this ?

Sub test()

    Dim rngA, rngB, c As Range
    Dim wsA As Worksheet
    Set wsA = Worksheets("SheetA")
    Set rngA = Worksheets("SheetA").Range("rngA")
    Set rngB = Worksheets("SheetB").Range("rngB")
    
    For Each c In rngA
        
        If Intersect(c, wsA.Range(rngB.Address)) Is Nothing Then
            c.EntireRow.Delete
        End If
        
    Next c

End Sub

 

[xzaqus]

My bad! The sentence was ambiguous.

My bad, I minsinterpreted this sentence in your initial post :



You actually wanted to change the reference of rngA, not the values of the cells.
Sorry for the miscomprehension.

What is strBuffer for?

It's a technique i like to use to select every cell with a particular criteria in 1 line of code.
strBuffer will contain the address of every cell that needs to be deleted, then with Range(strBuffer) you can operate on every cell at the same time.