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

keresztesi · Dec 13, 2017

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 · Dec 13, 2017

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 · Dec 13, 2017

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

keresztesi · Dec 13, 2017

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

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

Fluff · Dec 13, 2017

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 · Dec 13, 2017

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

Fluff said:
Do you have something like this
Excel 2013 32 bit

A B C D E F G H I
Postcode In Use? Latitude Longitude Easting Northing GridRef County District
AL1 5EG Yes TL159070 Hertfordshire St Albans
AL10 9WX Yes TL219072 Hertfordshire Welwyn Hatfield
AL2 1UX Yes TL166039 Hertfordshire St Albans
AL2 2EJ Yes TL157017 Hertfordshire Hertsmere

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]51.750185[/TD]
[TD="align: right"]-0.32129[/TD]
[TD="align: right"]515981[/TD]
[TD="align: right"]207054[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]51.750259[/TD]
[TD="align: right"]-0.234901[/TD]
[TD="align: right"]521944[/TD]
[TD="align: right"]207203[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]51.721887[/TD]
[TD="align: right"]-0.312479[/TD]
[TD="align: right"]516662[/TD]
[TD="align: right"]203921[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]51.702462[/TD]
[TD="align: right"]-0.326762[/TD]
[TD="align: right"]515725[/TD]
[TD="align: right"]201738[/TD]

</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 · Dec 13, 2017

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 · Dec 13, 2017

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

WBD

keresztesi · Dec 13, 2017

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

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

WBD

keresztesi · Dec 13, 2017

[TABLE="class:grid"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="5"><col span="5"><col></colgroup><tbody>[TR]
[TD]AZON[/TD]
[TD="align: left"]

Megrendelőnév

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

Fluff said:
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 · Dec 13, 2017

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

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

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

keresztesi

Board Regular

keresztesi

Board Regular

Excel Facts

Fluff

MrExcel MVP, Moderator

keresztesi

Board Regular

Fluff

MrExcel MVP, Moderator

keresztesi

Board Regular

Fluff

MrExcel MVP, Moderator

wideboydixon

Well-known Member

keresztesi

Board Regular

keresztesi

Board Regular

Fluff

MrExcel MVP, Moderator

Forum statistics

Share this page

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

Board Regular

Board Regular

Excel Facts

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Board Regular

MrExcel MVP, Moderator

Well-known Member

Board Regular

Board Regular

MrExcel MVP, Moderator

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock