Identify Cells with value of 0 and delete 0 cell and adjacent cell

Hello,

I am having trouble writing a macro that both identifies cells with a value of 0 within a range and then deletng that cell and the cell to the immediate left. Ideally this macro would clear both cells and move the rest of the data up one row.

Thank you!

Sam

Hi Sam.

You need a select special Macro.

I think that you want just those two cells deleted not the entire row.

BRB

Correct, I only want those two cells deleted so that the cells below them will move up. I need the data to be without blanks so that I can bring it over to another worksheet without causing system issues after the workbook is finalized.

Here is your code.

Enjoy.

Sub Select_Special()
Dim rngFind As Range
    Dim strValueToPick As String
    Dim rngPicked As Range
    Dim rngLook As Range
    Dim strFirstAddress As String

    Range("A:A").Select
    
    Set rngLook = Selection
    strValueToPick = "0"
    With rngLook
        Set rngFind = .Find(strValueToPick, .Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
        If Not rngFind Is Nothing Then
            strFirstAddress = rngFind.Address
            Set rngPicked = Range(rngFind, Cells(rngFind.Row, rngFind.Column + 1))
            Do
                Set rngPicked = Union(rngPicked, Range(rngFind, Cells(rngFind.Row, rngFind.Column + 1)))
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
        End If
    End With
    
    If Not rngPicked Is Nothing Then
        rngPicked.Select
    Selection.Delete Shift:=xlUp
    End If
    

End Sub

The line

Range("A:A").Select

Is where you select your range, modify that to suit your requirement.

Thank you mehmetcik!