Searching for a value in another worksheet and hiding row if it is not present in worksheet

Question

I am trying to write some VBA code to check numbers in one worksheet against numbers in another worksheet. I am trying to get excel to hide rows in one worksheet, if it is not present in another worksheet. I have written some code but I can not seem to get it functioning, any advice would be appreciated. The VBA code is attached underneath.

Sub HideCells()

Dim xlRange As Range
Dim xlCell As Range
Dim xlSheet As Worksheet
Dim valueToFind
Dim i As Long


For i = 2 To Rows.Count

valueToFind = Sheets("køb total").Cells(i, 1).Value
Set xlSheet = ActiveWorkbook.Worksheets("Køb VT nummer")
Set xlRange = xlSheet.Range("A1:A50000")

For Each xlCell In xlRange
    If xlCell.Value = valueToFind Then

        Else
        Worksheets("Køb total").Rows("i").EntireRow.Hidden = True

    End If
Next xlCell
Next i

End Sub

Answer 1

Couple changes - removed the quotation marks from around i in your If statement, moved some Sets outside the loop, and changed your If/Else statement to one case of If Not:

Sub HideCells()

Dim xlRange As Range
Dim xlCell As Range
Dim xlSheet As Worksheet, sht As Worksheet
Dim valueToFind
Dim i As Long, lastrow As Long, lastrow2 As Long

Set xlSheet = ActiveWorkbook.Worksheets("Køb VT nummer")
lastrow = xlSheet.Cells(xlSheet.Rows.Count, "A").End(xlUp).Row
Set xlRange = xlSheet.Range("A1:A" & lastrow)

Set sht = ActiveWorkbook.Worksheets("køb total")
lastrow2 = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

Debug.Print lastrow
Debug.Print lastrow2

For i = 2 To lastrow2

valueToFind = Sheets("køb total").Cells(i, 1).Value

    For Each xlCell In xlRange
        If Not xlCell.Value = valueToFind Then
            Worksheets("Køb total").Rows(i).EntireRow.Hidden = True
            Exit For
        End If
    Next xlCell

Next i

End Sub

Although I think you still need to replace Rows.Count with lastrow, you should make sure that is equaling what you think it is (by stepping through your code with F8, running your cursor over Rows.Count and seeing what its value is).

Answer 2

Try this:

Sub HideCells()

Dim xlRange As Range
Dim xlCell As Range
Dim xlSheet As Worksheet
Dim valueToFind
Dim i As Long
Dim bExists As Boolean


For i = 2 To Rows.Count

    valueToFind = Sheets("køb total").Cells(i, 1).Value
    Set xlSheet = ActiveWorkbook.Worksheets("Køb VT nummer")
    Set xlRange = xlSheet.Range("A1:A50000")

    For Each xlCell In xlRange

        If xlCell.Value = valueToFind Then

            bExists = True
            Exit For
        End If
    Next xlCell

    If Not bExists Then
        Worksheets("Køb total").Rows("i").EntireRow.Hidden = True
    End If
Next i

End Sub