Find multiple values then delete cell and the next cell to the right

daren.beaney · Jun 23rd 2011

Hi,

I'm struggling with an issue I have deleting cells with specific criteria. The criteria is "Payslips", "Salary", "No pay / reduced pay" which may or may not be listed in column "S". Column "S" contains unique values.
If a cell within column "S" contains one of the above criteria, I need it to then delete that cell and offset(0'1)

I've tried various options but majority of what's out there deletes the entire row.

Thanks,
Daren

Attilas · Jun 24th 2011

Re: Find multiple values then delete cell and the next cell to the right

Try

Code

Option Explicit
Sub DeleteCellsWCriteria()
Dim rng As Range, iCell As Range
Set rng = Range("S:S").SpecialCells(xlCellTypeConstants)
    For Each iCell In rng
        If InStr(1, iCell.Value, "Payslips") > 0 Or InStr(1, iCell.Value, "Salary") > 0 Or InStr(1, iCell.Value, "No pay / reduced pay") > 0 Then
            Range(iCell, iCell.Offset(0, 1)).Delete (xlShiftToLeft)
            'Range(iCell, iCell.Offset(0, 1)).ClearContents (xlShiftToLeft)
        End If
    Next iCell
End Sub

Display More

HTH

Regards

Attila

daren.beaney · Aug 24th 2011

Re: Find multiple values then delete cell and the next cell to the right

Hi,I tried your code and the next issue is that it deletes any cells that contain the criteria. How do I change the code so that it only selects the cells that match the criteria?

jindon · Aug 24th 2011

Re: Find multiple values then delete cell and the next cell to the right

try

Code

Sub test()
Dim i As Long
For i = Range("s" & Rows.Count).End(xlUp).Row To 1 Step -1
    Select Case Cells(i, "s").Value
        Case "Payslips", "No pay / reduced pay", "Salary"
            Cells(i, "s").Resize(, 2).Delete xlShiftToLeft
    End Select
Next
End Sub

daren.beaney · Aug 24th 2011

Re: Find multiple values then delete cell and the next cell to the right

Thanks for the reply!What if i need it to start in cell S6?

jindon · Aug 24th 2011

Re: Find multiple values then delete cell and the next cell to the right

Replace "1" with "6"
i.e.

Code

For i = Range("s" & Rows.Count).End(xlUp).Row To 6 Step -1

If you want to Select then

Code

Sub test() 
    Dim i As Long, x As Range
    For i = Range("s" & Rows.Count).End(xlUp).Row To 6 Step -1 
        Select Case Cells(i, "s").Value 
        Case "Payslips", "No pay / reduced pay", "Salary" 
            If x Is Nothing Then
                Set x = Cells(i,"s").Resize(,2)
            Else
                Set x = Union(x,Cells(i,"s").Resize(,2)) 
        End Select 
    Next 
    If Not x Is Nothing Then x.Select
End Sub

Display More

daren.beaney · Aug 24th 2011

Re: Find multiple values then delete cell and the next cell to the right

Thanks,I tried the code and I get an 'End Select' error as if it can't see the Select Case.

jindon · Aug 24th 2011

Re: Find multiple values then delete cell and the next cell to the right

OOps,

Missed an End If

Code

Sub test() 
    Dim i As Long, x As Range 
    For i = Range("s" & Rows.Count).End(xlUp).Row To 6 Step -1 
        Select Case Cells(i, "s").Value 
        Case "Payslips", "No pay / reduced pay", "Salary" 
            If x Is Nothing Then 
                Set x = Cells(i,"s").Resize(,2) 
            Else 
                Set x = Union(x,Cells(i,"s").Resize(,2)) 
            End If  '<-  this one
            End Select 
        Next 
        If Not x Is Nothing Then x.Select 
    End Sub

Display More

daren.beaney · Aug 25th 2011

Re: Find multiple values then delete cell and the next cell to the right

Thanks for you help on this it worked a treat!

Find multiple values then delete cell and the next cell to the right

Participate now!

Share