VBA - match criteria then delete specific cells (not entire rows!)

[keresztesi]

Hi,

I'd like to have a vba code for that problem.

If column "R" contains whether "xxx" or "yyy" or "zzz" then delete from the specific rows thw following cells: U:AD and AV:BE and BW:CF and CX:DG.

So, for example in column "R" it finds "xxx" in row number 15 and "yyy" in row number 22, than it clears the following cells:
U15:AD15 and U22:AD22
AV15:BE15 and AV22:BE22
BW15:CF15 and BW22:CF22
CX15:DG15 and CX22:DG22

Thx

 

[keresztesi]

With these lines it also gives me the syntax error:

Public Sub ClearSomeCells()


Application.Calculation = xlManual


Dim lastRow As Long


lastRow = Cells(Rows.Count, "R").End(xlUp).Row
With Range("A1:DG" & lastRow)
.AutoFilter Field:=18, Criteria1:=Array("5.régi", "4.lejárt"), Operator:=xlFilterValues
.SpecialCells(xlCellTypeVisible).Range("U:AD").ClearContents
.SpecialCells(xlCellTypeVisible).Range("AV:BE").ClearContents
.SpecialCells(xlCellTypeVisible).Range("BW:CF").ClearContents
.SpecialCells(xlCellTypeVisible).Range("CX:DG").ClearContents
.AutoFilter
End With


Application.Calculation = xlAutomatic


End Sub

 

[Fluff]

Do you have a header row?
If so what row is it in?

 

[keresztesi]

I don't exactly understand your question.
Do you need to know what is it in row1 or what?

Do you have a header row?
If so what row is it in?

 

[Fluff]

Do you have something like this

Excel 2013 32 bit
	A	B	C	D	E	F	G	H	I
1	Postcode	In Use?	Latitude	Longitude	Easting	Northing	GridRef	County	District
2	AL1 5EG	Yes	51.750185	-0.32129	515981	207054	TL159070	Hertfordshire	St Albans
3	AL10 9WX	Yes	51.750259	-0.234901	521944	207203	TL219072	Hertfordshire	Welwyn Hatfield
4	AL2 1UX	Yes	51.721887	-0.312479	516662	203921	TL166039	Hertfordshire	St Albans
5	AL2 2EJ	Yes	51.702462	-0.326762	515725	201738	TL157017	Hertfordshire	Hertsmere
Postcodes

Where the first row contains a header, or description of what that column holds, with the actual data starting in row 2?

 

[keresztesi]

Not exactly.
It contains texts and functions, too. And there are some merged cells as well.

Do you have something like this
Excel 2013 32 bit

	A	B	C	D	E	F	G	H	I
1	Postcode	In Use?	Latitude	Longitude	Easting	Northing	GridRef	County	District
2	AL1 5EG	Yes	51.750185	-0.32129	515981	207054	TL159070	Hertfordshire	St Albans
3	AL10 9WX	Yes	51.750259	-0.234901	521944	207203	TL219072	Hertfordshire	Welwyn Hatfield
4	AL2 1UX	Yes	51.721887	-0.312479	516662	203921	TL166039	Hertfordshire	St Albans
5	AL2 2EJ	Yes	51.702462	-0.326762	515725	201738	TL157017	Hertfordshire	Hertsmere

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Postcodes​

Where the first row contains a header, or description of what that column holds, with the actual data starting in row 2?

 

[Fluff]

Could you supply a snapshot of your data similar to post#14?
there are some tools here to help
https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[wideboydixon]

Are you getting a syntax error or a 1004 error (AutoFilter method failed)? Is there anything in column A?

WBD

 

[keresztesi]

Run-time error '1004'
Column "A" isn't empty there are also values.

Are you getting a syntax error or a 1004 error (AutoFilter method failed)? Is there anything in column A?

WBD

 

[keresztesi]

AZON	Megrendelőnév <tbody> </tbody>	Cikk	Cikknév	PLU	db	kiállási időpont	MM	ok	kész	db ok?	BIZERBA 1	db	db	db	db	db	jav
363784_010	PENNY MARKET KFT.	8113	Trappista 1,25 kg darabolt *	701	12 480	2017-12-13 04:00	1,7	4.lejárt
363799_010	PENNY MARKET KFT.	8125	Edami 1,25 kg darabolt *	704	12	2017-12-13 04:00	0,0	4.lejárt
363799_020	PENNY MARKET KFT.	8129	Trappista light 1,4 kg *	702	8	2017-12-13 04:00	0,0	4.lejárt
363783_010	PENNY MARKET KFT.	8311	Trappista fel.egal.700g SISSY*	0	3 840	2017-12-13 04:00	0,2	4.lejárt				2					1 000
363727_010	TESCO GLOBAL ÁRUHÁZAK ZRT.	8103	Trappista 1,5 kg *	135	144	2017-12-13 06:00	0,0	4.lejárt			11					500
363712_010	PENNY MARKET KFT. /VESZPRÉMI/	8113	Trappista 1,25 kg darabolt *	172	3 324	2017-12-13 06:00	0,4	4.lejárt			12					100
363727_400	TESCO GLOBAL ÁRUHÁZAK ZRT.	8149	Grande sajt 1,25 kg *	707	72	2017-12-13 06:00	0,0	4.lejárt
363745_010	HÚS DEPÓ KFT. (E.M.É.K.)	8103	Trappista 1,5 kg *	100	64	2017-12-12 14:00	0,0	3.kész	x		13					50
363687_010	METRO KFT. MISKOLC	8103	Trappista 1,5 kg *	117	48	2017-12-12 14:00	0,0	3.kész	x
363687_020	METRO KFT. MISKOLC	8113	Trappista 1,25 kg darabolt *	118	48	2017-12-12 14:00	0,0	3.kész	x		15					20
363707_010	K-ALFI-KER KFT. /REÁL/ 2. SZ. RAKT	8141	Trappista 1,5 kg falusi REAL*	123	144	2017-12-12 14:00	0,0	3.kész	x
363690_010	METRO KFT. KELET-PEST	8103	Trappista 1,5 kg *	117	240	2017-12-12 21:00	0,0	3.kész	x
363681_010	METRO KFT. BUDAPEST /FERENCVÁROS/	8103	Trappista 1,5 kg *	117	96	2017-12-12 21:00	0,0	3.kész	x		19					10
363681_020	METRO KFT. BUDAPEST /FERENCVÁROS/	8113	Trappista 1,25 kg darabolt *	118	48	2017-12-12 21:00	0,0	3.kész	x
363568_020	METRO KFT. BUDAKALÁSZ	8149	Grande sajt 1,25 kg *	716	12	2017-12-12 21:00	0,0	3.kész	x

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="5"><col span="5"><col></colgroup><tbody>
</tbody>

Could you supply a snapshot of your data similar to post#14?
there are some tools here to help
https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[Fluff]

Judging by that you want to filter on col I not R, if that's right try this modified version of WBD's code

Public Sub ClearSomeCells()

Dim lastRow As Long

lastRow = Cells(Rows.Count, "R").End(xlUp).row
Range("A1:DG1").AutoFilter Field:=9, Criteria1:=Array("4.lejárt", "5.régi"), Operator:=xlFilterValues
With Range("A2:DG" & lastRow)
    .SpecialCells(xlCellTypeVisible).Range("U:AD").ClearContents
    .SpecialCells(xlCellTypeVisible).Range("AV:BE").ClearContents
    .SpecialCells(xlCellTypeVisible).Range("BW:CF").ClearContents
    .SpecialCells(xlCellTypeVisible).Range("CX:DG").ClearContents
    .AutoFilter
End With

End Sub