0

sorry to bother with question, but I can not find any solution for my issue no matter what I try. What I would like to do:

  1. look at range in colum A in sheet1 which can vary in number of lines. All cells in Worksheets("Sheet1").Range("A:A") contains text.

  2. take sheet1 range and for all cells in that range look at range in sheet2 Worksheets("Sheet2").Range("A4:Axx") number of lines may vary also and starts at A4.

  3. then if the cell text from range1 match in cell of range2 then I need to keep that line and rest of lines in range2 (A4:Axx) what A colum value dont match the A column from sheet1 should be deleted.


I study it a lot lately but it is beyond my capability :(

I suppose it will be based on "For Each c In Range1" find ..... but since there are 2 ranges in different sheet and not single values - I am stucked.

Really appreciate any help. Thank you a lot in advance.


Here is the code I find out:

Sub KRITICKE_LH()

        Dim A As String
        Dim BODY As Range
        Dim CMM As Range
        Dim F As Range
    
    Application.ScreenUpdating = False
    
    Sheets("3D LH").Select
    Range("A4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Set CMM = Selection
    Range("A4").Select
    
    Sheets("BODY CAP").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Set BODY = Selection
    Range("A1").Select
       
                
        For Each c In CMM
            If IsError(Application.Match(c.Value, _
            BODY, 0)) Then
                If F Is Nothing Then
                    Set F = c
                Else
                    Set F = Union(F, c)
                End If
            End If
        Next
            
        If Not F Is Nothing Then F.EntireRow.Delete xlUp

Application.ScreenUpdating = True

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
Vecernice
  • 5
  • 3

0 Answers0