VBA-only delete rows based off cells that is not in another sheet

[Registered55]

Hello,

Sheet2 column a has strings that i want to be saved from sheet1

Sheet1 if column H does NOT contain string that is in sheet2 column A
then delete row.

So basically sheet2 contains the list of what i want to keep in sheet1, everything else delete.

sheet2 ColA needs to be dynamic as the list wil grow and shrink accordingly to changes i make.

Thanks.

Sheet1 contains around 10k rows.

 

[Registered55]

Hello i think i did this right, the first screen is the CSV data in it's original form:

BookingList_20200316.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1123	196819	Individual	133305	LEWIS	DAFYDD			JCHLB	########	########	1	344.95			344.95	########	0	PREPAY	1	<B>R/BILL:</B> APOL (CRS)	DBL	TD	Great Britain	RA4	1	Undefined
1124	198089	Individual	134162	MCNALLY	MICHAEL PATRICK		329	JCWLD	########	########	1	138				########	0	CC	1	<B>R/INTERN:</B> BB TO CC 0329*VT (USER)	DBL	CI	Great Britain	CX01	1	Undefined
1125	195059	Individual	132215	AIRSTONE	ANGELA		3339	JBGLB	########	########	1	252.9				########	0	CC	1	<B>R/HOTEL:</B> Non-Smoking (CRS)<br/><B>R/FORCG:</B> OTA Desync-CC expiration date (CRS) <span style=font-family: wingdings font-size: 300%">&#252</span><br/><B>R/GUEST:</B> ** Genius Booker **
1126	Do you have gluten free breakfast options?
1127	Thank you. (CRS)"	DBLM	TE	Great Britain	FLRB1	1	Undefined
1128	200631	Individual	135807	POOLE	AMY		7506	JFFLB	########	########	1	459.72				########	0	CC	1	<B>R/HOTEL:</B> 1 double bed Non-Smoking Expedia Virtual Card will be activated from the day of Check-in. - - POS : Hotels.com (CRS)<br/><B>R/BILL:</B> Net amounts - discount already deducted (CRS)	DBL	TH	USA	FLRA4S	1	Undefined
1129	190390	Individual	129133	THOMPSON-KELLY	CLAIRE			HSRLC	########	########	1	238.45			238.45	########	0	PREPAY	1	<B>R/BILL:</B> APOL_Multiple rooms (CRS)	TWI	TC	Switzerland	RB4	1	Undefined
1130	190391	Individual	129133	THOMPSON-KELLY	CLAIRE			HSRLC	########	########	1	238.45			238.45	########	0	PREPAY	1	<B>R/BILL:</B> APOL_Multiple rooms (CRS)	TWI	TC	Switzerland	RB4	1	Undefined
BookingList_20200316

you can see that i have a few lines that need to be fixed, so i use the code

Sub Removebadlines()
    LR3 = Range("A" & Rows.Count).End(xlUp).Row

    For i3 = LR3 To 2 Step -1
        If IsNumeric(Range("A" & i3).Value) And _
        Len(Range("A" & i3).Value) > 0 Then
        Else
            Rows(i3).Delete
        End If
    Next i3
End Sub

which i'm starting to see this could be one cause of issue?


however without you code running, i get this

BookingList_20200316.csv
	A	B	C	D	E	F	G	H
1122	LEWIS	JCHLB	#############	344.95	344.95	PREPAY	Great Britain	RA4
1123	MCNALLY	JCWLD	#############	138		CC	Great Britain	CX01
1124	AIRSTONE	JBGLB	#############	252.9		CC
1125	POOLE	JFFLB	#############	459.72		CC	USA	FLRA4S
1126	THOMPSON-KELLY	HSRLC	#############	238.45	238.45	PREPAY	Switzerland	RB4
1127	THOMPSON-KELLY	HSRLC	#############	238.45	238.45	PREPAY	Switzerland	RB4
BookingList_20200316

notice the blank cells in the rates column, now before your macro, we would use the autofilter and manually sellect only the rates that we want to be shown...but this has lead to human error, because it's easy to select the wrong one from the autofilter and by doing so we accidentally charge guests a booking that is pay on arrival...not pre-paid.

so i want a way to remove any "rates" from the list that doesn't belong so there by elimating human error.

this is when i asked for help and you then suggested your code for me.

however i don't know why, but for some reason a few rows are being removed from the main list that should not be?

if you like, i can send you my files using a secure link if you have one?

thanks.

 

[Peter_SSs]

if you like, i can send you my files using a secure link if you have one?

Sorry, you can't send the file to me. Forum correspondence needs to be kept public - refer to #4 of the Forum Rules
You can upload a sample file (I would suggest much less than 1,000 rows & with any sensitive data removed or disguised) to a file-share site like DropBox or use OneDrive/Google drive etc and provide a link here.

However, the problem for me is not the file, it is understanding the problem & that requires you to clarify.
Statements like ..

you can see that i have a few lines that need to be fixed

.. don't clarify because you might know what to look at to know what needs fixing but I don't. You need statements like "rows 10 and 22 are wrong because they have dates in column D when they should have names. We need to fix that by removing those rows"

Another confusing thing for me is that your second screen shot bears no resemblance to the first. The first has mostly numerical data and the code shown apparently is to remove the non-numerical rows (but you haven't specifically stated that) yet the second screen shot has names in column A.

What might be more help is to move away from showing things that do not work and show
- what you have to start with
- what you want to end with and how you would get from the first to the second if you were doing it manually

BTW for that sample data shown in the second screen shot in post #21, the code I suggested early in thread deletes rows 1123 & 1124 (since CX01 and 'blank' do not appear in the Sheet2 list you provided).
Should either of those rows be kept? If so which one(s) and why?
Should any of the other rows show in that screen shot be deleted? If so, which ones and why?

 

[Registered55]

hello,

i'm not even expericienced enough to explain this, i'm clearly outside of my depth.

i really want to say thank you for your time, i will perhaps try a different approach...maybe

"advanced Filter" with range list...although so far this seems to be hiding every row in the entire table except the header and 1 row? not sure, but i only found this method last night, so much to learn with this approach.

i will continue to play, but worse case scenario my team still has the original macros.

appreciate your time, thanks.
perhaps in a year or two... i try this method again.

 

[Peter_SSs]

i'm not even expericienced enough to explain this,

Sorry I couldn't understand better what you needed.
Good luck with your project and post again if you need further help.