CODEWITHSUNDEEP
vbaexceldel
Kevin Le
Kevin Le

Reputation: 49

Deleting Specific Cells Excel

What I'm trying to do is have the program go through is find a certain value from within a column of cells and see if it matches with one cell after I special paste the value, and if there is a match delete the associated cell and its row of cells. What is happening is that the special pasting part of the program is working, but the associated cells are not being deleted. To clarify, I'm trying to delete a whole row from a certain column based on whether there's a match

Dim j As Integer
Dim i As Integer
i = 2
Dim Aud_Tot As Integer
Aud_Tot = Application.InputBox("How big is your audit", , , , , , , 1)
Do While True
    If Cells(i, 1).Value <> "" And Not IsError(Cells(i, 2).Value) Then
        Range(Cells(i, 1), Cells(i, 22)).Copy
        Range(Cells(i, 1), Cells(i, 22)).PasteSpecial xlPasteValues
        For j = 2 To Aud_Tot
            If Cells(j, 24).Value = Cells(i, 2).Value Then
                Range(Cells(j, 24), (Cells(j, 42))).ClearContents
            End If
        Next j
        i = i + 1
    Else
        Exit Do
    End If
Loop

Upvotes: 0

Views: 3308

Answers (1)

tjb1
tjb1

Reputation: 757

It seems you want to delete the row but you are only using ClearContents. To delete you can change that line to Range(Cells(j, 24), (Cells(j, 42))).Delete Shift:=xlShiftUp, you can also use xlShiftToLeft.

Did you want to delete the entire row or just the range you have?

Upvotes: 1

Related Questions