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
  • 10,905
  • 5
  • 18
  • 32
Vecernice
  • 1
  • 1
  • 2
    What code have you tried so far? Where did you run into trouble with it? Please include that in your question. – braX Jan 03 '22 at 10:55
  • 1
    Hureka!! I acctually solved it yesterday!! – Vecernice Jan 04 '22 at 08:30
  • I recommend you to read up on [how to avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Using `Select` in general is a bad practice and should be avoided. – Raymond Wu Jan 04 '22 at 08:50
  • @Raymond Perfect advice. Ill look at it, thank you. – Vecernice Jan 05 '22 at 07:19

0 Answers0