I am using the code below to combine two lists. (PS. found it on this site - thanks much!)
List 1 is in columns B:D and is a predetermined list of qualifying schools. Column B contains those school names. Columns C and D are used for data entry of an address and contact for each school, but not every school will have data entered for it.
List 2 is a dynamic list of non-qualifying schools in the same format; column F is the school name, column G is the address, and column H is the contact. Any school being added to the database that is not on the qualifying list gets added to this list along with data for its address and contact.
The code works in that it creates one sorted list made up of every school in the qualifying list 1 and the schools entered in the non-qualifying list 2.
I'd like to modify it so that the combined list only takes schools from list 1 if data has been entered for them in columns C and D.
There's probably and easy way to do this, but I'm stumped. Any help would be appreciated.
List 1 is in columns B:D and is a predetermined list of qualifying schools. Column B contains those school names. Columns C and D are used for data entry of an address and contact for each school, but not every school will have data entered for it.
List 2 is a dynamic list of non-qualifying schools in the same format; column F is the school name, column G is the address, and column H is the contact. Any school being added to the database that is not on the qualifying list gets added to this list along with data for its address and contact.
The code works in that it creates one sorted list made up of every school in the qualifying list 1 and the schools entered in the non-qualifying list 2.
I'd like to modify it so that the combined list only takes schools from list 1 if data has been entered for them in columns C and D.
There's probably and easy way to do this, but I'm stumped. Any help would be appreciated.
Code:
Dim LastRow1 As Long
Dim LastRow2 As Long
LastRow1 = Range("B" & Rows.Count).End(xlUp).Row
LastRow2 = Range("F" & Rows.Count).End(xlUp).Row
Range("B8:B" & LastRow1).Copy Range("O8")
Range("F8:F" & LastRow2).Copy Range("O" & LastRow1 + 1)
Range("O8:O" & LastRow1 + LastRow2 + 1).AdvancedFilter xlFilterCopy, , Range("P8"), True
Range("P8:P" & LastRow1 + LastRow2 + 1).Sort Key1:=Range("P8"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("O:O").Delete