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]

It gives me "run-time error" again.
The problem is with the autofilter method, it says.

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

 

[keresztesi]

And I definitely like to filter in column "R", other columns were hidden, that's why you've counted "I" as the 9th col.

 

[wideboydixon]

The method as originally posted worked on some test data that I put together but it's impossible to say what the problem might be with the AutoFilter method without having access to the original data.

WBD

 

[keresztesi]

Can I send my excel file directly to you?

The method as originally posted worked on some test data that I put together but it's impossible to say what the problem might be with the AutoFilter method without having access to the original data.

WBD

 

[wideboydixon]

You can put it on dropbox or some other file sharing site and send a link.

WBD

 

[keresztesi]

Here it is:
https://www.dropbox.com/s/87rvah3cbuzm272/ütemező_v7.2.xlsm?dl=0

 

[wideboydixon]

I think that perhaps there was already a filter in place. Try this code instead:

Public Sub ClearSomeCells()

Dim lastRow As Long

' Clear autofilter if necessary
If ActiveSheet.AutoFilterMode Then Range("A1").AutoFilter

' Find the last rrow
lastRow = Cells(Rows.Count, "R").End(xlUp).Row
Range("A1:DG" & lastRow).AutoFilter Field:=18, Criteria1:=Array("4.lejárt", "5.régi"), Operator:=xlFilterValues

' Clear the contents
Range("U2:AD" & lastRow).SpecialCells(xlCellTypeVisible).ClearContents
Range("AV2:BE" & lastRow).SpecialCells(xlCellTypeVisible).ClearContents
Range("BW2:CF" & lastRow).SpecialCells(xlCellTypeVisible).ClearContents
Range("CX2:DG" & lastRow).SpecialCells(xlCellTypeVisible).ClearContents

' Turn off autofilter
Range("A1").AutoFilter

End Sub

WBD

 

[keresztesi]

WBD,

you are a LEGEND!!!!
It works perfectly, smooth and fast!

Thank you!

I think that perhaps there was already a filter in place. Try this code instead:

Public Sub ClearSomeCells()

Dim lastRow As Long

' Clear autofilter if necessary
If ActiveSheet.AutoFilterMode Then Range("A1").AutoFilter

' Find the last rrow
lastRow = Cells(Rows.Count, "R").End(xlUp).Row
Range("A1:DG" & lastRow).AutoFilter Field:=18, Criteria1:=Array("4.lejárt", "5.régi"), Operator:=xlFilterValues

' Clear the contents
Range("U2:AD" & lastRow).SpecialCells(xlCellTypeVisible).ClearContents
Range("AV2:BE" & lastRow).SpecialCells(xlCellTypeVisible).ClearContents
Range("BW2:CF" & lastRow).SpecialCells(xlCellTypeVisible).ClearContents
Range("CX2:DG" & lastRow).SpecialCells(xlCellTypeVisible).ClearContents

' Turn off autofilter
Range("A1").AutoFilter

End Sub

WBD