VBA CODE TO HIDE ROW BASED ON VLOOKUP RESULT

jpbonono

Board Regular
Joined
Aug 18, 2013
Messages
154
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.


1609138200971.png
1609138238260.png


Thank you in advance.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,089
Please use the xl2BB add-in so you can copy the data from your worksheet into your post.
1609139386566.png
 

jpbonono

Board Regular
Joined
Aug 18, 2013
Messages
154
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:

PHASEBLOCKLOTAREAPRICE/SQMTYPETCP
I320576,250.00CORNER356,250.00
I34636,000.00INNER378,000.00
I316606,000.00INNER360,000.00
I43596,250.00CORNER368,750.00
I42586,000.00INNER348,000.00
I45626,000.00INNER372,000.00
I47616,000.00INNER366,000.00
I49606,000.00INNER360,000.00
I411606,000.00INNER360,000.00
I413735,500.00INNER401,500.00
I415735,500.00INNER401,500.00
I51735,750.00CORNER419,750.00
I527736,250.00CORNER456,250.00
I52506,000.00INNER300,000.00
I53506,000.00INNER300,000.00
I54506,000.00INNER300,000.00
I55506,000.00INNER300,000.00
I56506,000.00INNER300,000.00
I57506,000.00INNER300,000.00
I58506,000.00INNER300,000.00
I59506,000.00INNER300,000.00
I510506,000.00INNER300,000.00
I511506,000.00INNER300,000.00
I512506,000.00INNER300,000.00
I513506,000.00INNER300,000.00
I514506,000.00INNER300,000.00
I515506,000.00INNER300,000.00
I516506,000.00INNER300,000.00
I517506,000.00INNER300,000.00
I518506,000.00INNER300,000.00
I519506,000.00INNER300,000.00



ACCOUNT NAMEPHASEBLOCKLOTAREA
Apaitan, Sheila S.II4152
Rodrigo, Alberto C.I95131
Balila, Rolando A.I97131
Cheng, Jesus Charlie C.I93123
Cena, Rhodora R.I94132
Cena, Christine Marie D.I8360
Badua, Daisy B.IV6260
Palma, Francisca G.I41373
Palma, Francisca G.I41573
Villanueva, AmaliaIII6275
Atilano, Gemalin G.IV84266
Sulayao, Gelai G.IV84060
Eugenio, Gretchen G.IV6160
Patingo, Shanmealoe R.III8670
 

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,089
Just to be clear, you want to hide the item rows on the material sheet that has been sold.
 

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,089

ADVERTISEMENT

VBA Code:
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

Board Regular
Joined
Aug 18, 2013
Messages
154
VBA Code:
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

Well-known Member
Joined
Feb 26, 2006
Messages
1,089

ADVERTISEMENT

Your lots are "C" & "D"
 

Watch MrExcel Video

Forum statistics

Threads
1,129,277
Messages
5,635,242
Members
416,848
Latest member
Excel DG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top