finding cell value and delete it in excel vba-CodePudding

Hi I'm if I want to return the value of the cell it's found in VBA and clear all of it's content with another 3 row below it's as well, but i'm currently stuck since it can find where the cell coming form but deleted the whole range instead of it's specific range (i'm aware the range("A7:H20") was wrong, thanks guys if you can help

Sub Find_and_remove()
For Each cell In Range("A7:H20")
If cell.Value = Range("Q5") Then
'return the range of that cell and deleted it with 3 row below it'
Range("A7:H20").Clear
End If
Next cell
End Sub

CodePudding user response：

Sub Find_and_remove()
Dim rng As Range

For Each rng In Range("A7:H20")
    If rng.Value = Range("Q5") Then Range(rng, rng.Offset(3, 0)).Clear
Next cell

End Sub

CodePudding user response：

You could just use cell.Clear, or if you want the cell cleared and the next 3 below it use something like this

For i = 0 To 3
    cell.Offset(i, 0).Clear
Next

CodePudding user response：

I think you mean "return the address of that cell", no? Debug.Print(cell.Address) will get you this info. But you don't actually need it here. Instead of Range("A7:H20").Clear write cell.Resize(1 i, 1).Clear with i = number of rows you want to clear along with cell itself (no need for a loop).

CodePudding user response：

Another solution: I am using a sub to which you pass the parameters:

value to be found
range where to look in and clear contents
number of rows below the found value to be cleared.

Furthermore I am looking from the bottom to the top of the range - otherwise cells could be cleared that contain the string to be found - and then below values won't get cleared:

Option Explicit

'>>> example of how to call the findAndRemove-Sub <<<

Public Sub test_FindAndRemove()

With ActiveSheet   ' adjust this to your needs
    findAndRemove .Range("Q5"), .Range("A7:H20"), 3
End With

End Sub


'>>>> this is the sub that is doing the work <<<<<

Public Sub findAndRemove(strFind As String, _
    rgLookup As Range, _
    cntDeleteRowsBelow As Long)

Dim i As Long, c As Range

'start from the bottom and go up
'otherwise you could delete further strFind-cells unintentionally
For i = rgLookup.Rows.Count To 1 Step -1
    For Each c In rgLookup.Rows(i).Cells
        If c.Value = strFind Then
            'ATTENTION:
            'at this point there is no check if below row contains the strFind-value!
            c.Resize(cntDeleteRowsBelow   1).Clear
        End If
    Next
Next

End Sub