VBA code to hide rows and for Vlookup

S

shahid5788

Banned
Joined
Jun 2, 2017
Messages
5
Reaction score
0
Points
0
Hi,

Can you please assist me with a VBA macro code to my current process to make my data more efficient since I have a large amount of data. I have listed the steps below in order. Please see below. Can you please assist?


1 Need to hide all the rows that have "Account Number" range 1-3999
2 Need to hide all rows that have ID "28"
3 Need to hide all rows that have segment "3"
4 If "ID" is "22" AND "Location is "Blank" then State is CA
5 If "ID" is "22" then lookup "Location" AND "Client ID" from Sheet 1 to Sheet 2 and pick up the State and Country from Sheet 2 in column L and M
 

Attachments

  • Trying to Find VBA Code.xlsx
    11.5 KB · Views: 8
I see you've asked #5 in a separate thread.

This should look after 1-4.
Code: 
Sub testing_1()
    Dim rng As Range, cel As Range
    Dim lr As Long, accNum
    
Application.ScreenUpdating = False
With Sheets("Sheet1")
    lr = .Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = .Range("A2:A" & lr)
    
    For Each cel In rng
        accNum = Left(cel.Value, 4)
        If accNum < 4000 Or cel.Offset(0, 7) = 28 Or cel.Offset(0, 9) = 3 Then
            cel.EntireRow.Hidden = True
        End If
        
        If cel.Offset(0, 7).Value = 22 And cel.Offset(0, 4).Value = "" Then
                cel.Offset(0, 12).Value = "CA"
                cel.Offset(0, 13).Value = "US"
        End If
    Next cel
End With
Application.ScreenUpdating = True

End Sub
 
You didn't acknowledge my reply.
Now that I see (on at least 3 different forums) formulas that will do the trick, #5 is not difficult.
 
cross posted without links:
https://www.excelforum.com/excel-general/1189405-vba-code-to-hide-rows-and-for-vlookup.html
http://windowssecrets.com/forums/sh...eed-a-VBA-code-for-Current-Process?highlight=
https://www.mrexcel.com/forum/excel...asic-applications-code-hide-rows-vlookup.html


shahid5788, for your information, you should always provide links to your cross posts.
This is a requirement, not just a request.
If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184
 
You must log in or register to reply here.
Back
Top