VBA CODE TO HIDE ROW BASED ON VLOOKUP RESULT

[jpbonono]

Hello,

Hoping that someone can help me on this as I am working on a huge amount of data right now. So to beat the deadline, I would want to make everything easy by using a VBA code that would hide a row based on a VLOOKUP/HLOOKUP result (or other alternatives).

There are two sheets present, 1 is the masterlist of lots, while the 2nd is the sold lots list. I would like to make a more easy approach that whenever the PHASE BLOCK and LOT matches the one on the masterlist will automatically hide the matched row.

Thank you in advance.

 

[davesexcel]

Please use the xl2BB add-in so you can copy the data from your worksheet into your post.

 

[jpbonono]

Please use the xl2BB add-in so you can copy the data from your worksheet into your post.
View attachment 28652

Hello. Thank you.
here:

PHASE	BLOCK	LOT	AREA	PRICE/SQM	TYPE	TCP
I	3	20	57	6,250.00	CORNER	356,250.00
I	3	4	63	6,000.00	INNER	378,000.00
I	3	16	60	6,000.00	INNER	360,000.00
I	4	3	59	6,250.00	CORNER	368,750.00
I	4	2	58	6,000.00	INNER	348,000.00
I	4	5	62	6,000.00	INNER	372,000.00
I	4	7	61	6,000.00	INNER	366,000.00
I	4	9	60	6,000.00	INNER	360,000.00
I	4	11	60	6,000.00	INNER	360,000.00
I	4	13	73	5,500.00	INNER	401,500.00
I	4	15	73	5,500.00	INNER	401,500.00
I	5	1	73	5,750.00	CORNER	419,750.00
I	5	27	73	6,250.00	CORNER	456,250.00
I	5	2	50	6,000.00	INNER	300,000.00
I	5	3	50	6,000.00	INNER	300,000.00
I	5	4	50	6,000.00	INNER	300,000.00
I	5	5	50	6,000.00	INNER	300,000.00
I	5	6	50	6,000.00	INNER	300,000.00
I	5	7	50	6,000.00	INNER	300,000.00
I	5	8	50	6,000.00	INNER	300,000.00
I	5	9	50	6,000.00	INNER	300,000.00
I	5	10	50	6,000.00	INNER	300,000.00
I	5	11	50	6,000.00	INNER	300,000.00
I	5	12	50	6,000.00	INNER	300,000.00
I	5	13	50	6,000.00	INNER	300,000.00
I	5	14	50	6,000.00	INNER	300,000.00
I	5	15	50	6,000.00	INNER	300,000.00
I	5	16	50	6,000.00	INNER	300,000.00
I	5	17	50	6,000.00	INNER	300,000.00
I	5	18	50	6,000.00	INNER	300,000.00
I	5	19	50	6,000.00	INNER	300,000.00

ACCOUNT NAME	PHASE	BLOCK	LOT	AREA
Apaitan, Sheila S.	II	4	1	52
Rodrigo, Alberto C.	I	9	5	131
Balila, Rolando A.	I	9	7	131
Cheng, Jesus Charlie C.	I	9	3	123
Cena, Rhodora R.	I	9	4	132
Cena, Christine Marie D.	I	8	3	60
Badua, Daisy B.	IV	6	2	60
Palma, Francisca G.	I	4	13	73
Palma, Francisca G.	I	4	15	73
Villanueva, Amalia	III	6	2	75
Atilano, Gemalin G.	IV	8	42	66
Sulayao, Gelai G.	IV	8	40	60
Eugenio, Gretchen G.	IV	6	1	60
Patingo, Shanmealoe R.	III	8	6	70

 

[davesexcel]

Just to be clear, you want to hide the item rows on the material sheet that has been sold.

 

[davesexcel]

Sub HIdeRws()
    Dim sh As Worksheet, ws As Worksheet
    Dim rng As Range, c As Range
    Dim fRng As Range, f As Range

    Set sh = Sheets("sold lots")
    Set ws = Sheets("masterlist ")

    With sh
        Set rng = .Range("D2:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
    End With

    With ws
        Set fRng = .Range("C2:C" & .Cells(.Rows.Count, "C").End(xlUp).Row)
    End With

    For Each c In rng.Cells
        For Each f In fRng.Cells
            If c = f And c.Offset(, -1) = f.Offset(, -1) And c.Offset(, -2) = f.Offset(, -2) Then
                f.EntireRow.Hidden = True
            End If
        Next
    Next

End Sub

 

[jpbonono]

Sub HIdeRws()
    Dim sh As Worksheet, ws As Worksheet
    Dim rng As Range, c As Range
    Dim fRng As Range, f As Range

    Set sh = Sheets("sold lots")
    Set ws = Sheets("masterlist ")

    With sh
        Set rng = .Range("D2:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
    End With

    With ws
        Set fRng = .Range("C2:C" & .Cells(.Rows.Count, "C").End(xlUp).Row)
    End With

    For Each c In rng.Cells
        For Each f In fRng.Cells
            If c = f And c.Offset(, -1) = f.Offset(, -1) And c.Offset(, -2) = f.Offset(, -2) Then
                f.EntireRow.Hidden = True
            End If
        Next
    Next

End Sub

Hi, Thank you for this ill try this code, may I ask why is the range C and D? I apologize as I am only a beginner to VBA. I really appreciate your help. ^_^

 

[davesexcel]

Your lots are "C" & "D"

 

[jpbonono]

Your lots are "C" & "D"

i see. thank you. the condition includes the "phase" which means there are 3 conditions. can you add B please?

 

[davesexcel]

Test the code before commenting.

 

[jpbonono]

H

Test the code before commenting.

Hello Thank you. Tried running the code, its not doing anything. I tried change the range cells nothing happens. Its not hiding the row. that matches the sold lots.