Excel VBA Hidding rows by comparing two Cells

0
votes

I have a question about how to use a double loop to compare two Cells which are located in different sheets("Sheet1" and "Sheet2"). The condition I want to apply to the Loop is that in case if the two cells are different, the row must be hidden (Applied to the table located in Sheet1). In the contrary case, if the two cells are the same, the row stays as it is by default. But with the Macro I wrote, it hides all rows that form the Sheet1 table. What could be the reason?

  Sub HideRows()

Sheets("Sheet2").Select

Dim NR As Integer
NR = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))

Sheets("Sheet1").Select

Dim i As Integer, j As Integer

For i = 2 To 10
    For j = 1 To NR
        If Cells(i, 1) <> Sheets("Sheet2").Cells(j, 1) Then
            Rows(i & ":" & i).Select
            Selection.EntireRow.Hidden = True
        End If
    Next j
Next I    
End Sub

Sheet1:

Sheet1]

Sheet2:

![Sheet2]

Desired result:

Desired result

2
excelvba
What type of data are you comparing? (numbers, text, dates etc.) - I can only assume that Cells(i, 1) <> Sheets("Sheet2").Cells(j, 1) is evaluating to True for each iteration of your loop. To test this add Debug.Print Cells(i, 1) <> Sheets("Sheet2").Cells(j, 1) into your loop and check the results in the immediate window. – Samuel Everson
it hides all rows that form the Sheet1 table that's because you are comparing every row on Sheet1 to every row on sheet2 and hiding the row when they are not equal. the only case where all rows won't be hidden is if all rows on sheet1 and sheet2 are the same. Clearly that's not what you want, but it is not clear what you do want. Add some sample data, and the expected result (as text so we can copy paste for testing) – chris neilsen
Thank you for your responses, for better understanding. I am sharing a wetransfer link with the images of the desired result. wetransfer.com/downloads/… – barunsonn
@barunson since you're new here, a couple of pointers: 1) don't add extra info in comments, edit it into your Q instead. 2) don't add images as links to external sites. Few people will download those. You can insert an image into your Q. With only 1 rep they won't display, but other users with enough rep can edit to display them. 3) don't force us to retype your data. Include it as text so we can copy paste for testing – chris neilsen

2 Answers

0
votes

Your task is better described as

Hide all rows on Sheet1 whose column A value does not apear on Sheet2 column A

Using the looping the ranges technique you tried, this could be written as

Sub HideRows()
    Dim rng1 As Range, cl1 As Range
    Dim rng2 As Range, cl2 As Range
    Dim HideRow As Boolean
    
    With ThisWorkbook.Worksheets("Sheet1")
        Set rng1 = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    With ThisWorkbook.Worksheets("Sheet2")
        Set rng2 = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    
    rng1.EntireRow.Hidden = False
    For Each cl1 In rng1.Cells
        HideRow = True
        For Each cl2 In rng2.Cells
            If cl1.Value2 = cl2.Value2 Then
                HideRow = False
                Exit For
            End If
        Next
        If HideRow Then
            cl1.EntireRow.Hidden = True
        End If
    Next
            
End Sub

That said, while this approach is ok for small data sets, it will be slow for larger data sets.

A better approach is to loop Variant Arrays of the data, and build a range reference to allow hiding all required rows in one go


Sub HideRows2()
    Dim rng1 As Range, cl1 As Range, dat1 As Variant
    Dim rng2 As Range, cl2 As Range, dat2 As Variant
    Dim HideRow As Boolean
    Dim r1 As Long, r2 As Long
    Dim HideRange As Range
    
    With ThisWorkbook.Worksheets("Sheet1")
        Set rng1 = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        dat1 = rng1.Value2
    End With
    
    With ThisWorkbook.Worksheets("Sheet2")
        Set rng2 = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
        dat2 = rng2.Value2
    End With
    
    rng1.EntireRow.Hidden = False
    For r1 = 2 To UBound(dat1, 1)
        HideRow = True
        For r2 = 1 To UBound(dat2, 1)
            If dat1(r1, 1) = dat2(r2, 1) Then
                HideRow = False
                Exit For
            End If
        Next
        If HideRow Then
            If HideRange Is Nothing Then
                Set HideRange = rng1.Cells(r1, 1)
            Else
                Set HideRange = Application.Union(HideRange, rng1.Cells(r1, 1))
            End If
        End If
    Next
    
    If Not HideRange Is Nothing Then
        HideRange.EntireRow.Hidden = True
    End If
End Sub
-1
votes

@Chjris Neilsen has beaten me to most of what I wanted to mention. Please refer to his comment above. However, there are two things I want to add.

  1. Please don't Select anything. VBA knows where everything is in your workbook. You don't need to touch. Just point.
  2. i and j aren't really meaningful variable identifiers for Rows and Columns. They just make your task that much more difficult - as if you weren't struggling with the matter without the such extra hurdles.

With that out of the way, your code would look as shown below. The only real difference is the Exit For which ends the loop when the decision is made to hide a row. No guarantee that the procedure will now do what you want but the logic is laid are and shouldn't be hard to adjust. I point to .Rows(C).Hidden = True in this regard. C is not a row. It's a column.

Sub HideRows()

    ' always prefer Long datatype for rows and columns
    Dim Rl      As Long                 ' last row: Sheet2
    Dim C       As Long                 ' loop counter: columns
    Dim R       As Long                 ' loop counter: rows
    
    Rl = WorksheetFunction.CountA(Sheet2.Columns(1))
    With Sheet1
        For C = 2 To 10
            For R = 1 To Rl
                ' always list the variable item first
                If Sheets("Sheet2").Cells(R, 1).Value <> .Cells(C, 1).Value Then
                    .Rows(C).Hidden = True
                    Exit For
                End If
            Next R
        Next C
    End With
End Sub