CODEWITHSUNDEEP
excelvba
Lam Nguyen tung lam
Lam Nguyen tung lam

Reputation: 23

finding cell value and delete it in excel vba

I want to return the value of the cell it's found in VBA and clear all of it's content with another 3 rows below it as well, but I'm currently stuck. I can find where the cell is coming from, but it's deleting the whole range instead of the specific range (I'm aware the range("A7:H20") was wrong). How do I select the correct range?

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

Upvotes: 2

Views: 1732

Answers (4)

Ike
Ike

Reputation: 13014

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

Upvotes: 1

ouroboros1
ouroboros1

Reputation: 14064

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).

Upvotes: 0

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

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

Upvotes: 2

CHill60
CHill60

Reputation: 1958

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

Upvotes: 1

Related Questions