CODEWITHSUNDEEP
excelvba
Dmcovey1993
Dmcovey1993

Reputation: 21

Excel VBA Search And Delete

So I use 1dcutx program which shoots out spread sheets with information. At the moment I have it deleting some cells and shifting them up and then renaming the page. Some stuff changes location like it is not the same for every spread sheet. So what I need left is a way to search and Delete that cell and also in some cases the cell to the left of it as well.

Example: Utilization, %: Is in one cell 62.74 Would be right next to but this amount changes.

So I would need a search and delete and have it select the cell to the left and delete that one as well.

Here is what i have so far 

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("1D_report").Select
    Rows("3:9").Select
    Selection.Delete Shift:=xlUp
    Range("E1:F2").Select
    Selection.ClearContents
    Columns("H:H").Select
    Selection.ClearContents
    Sheets("1D_report").Select
    Sheets("1D_report").Name = "s_report"
End Sub

Upvotes: 0

Views: 622

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Give this a try:

Sub asdf()
    Dim r As Range
    Set r = Cells.Find(What:="Utilization, %", After:=Range("A1"))
    Range(r, r.Offset(0, 1)).Clear
End Sub

EDIT:

If you want to clear the cell to the LEFT of the found cell, use:

Offset(0,-1)

instead of:

Offset(0,1)

EDIT2:

I like several short macros rather than long ones so I would use something like:

Sub MAIN()
    Call Macro1
    Call asdf
End Sub

EDIT3:

Try this version - do you get the error message??

Sub asdf()
    Dim r As Range
    Dim s As String
    s = "Utilization, %"
    Set r = Cells.Find(What:=s, After:=Range("A1"))
    If r Is Nothing Then
        MsgBox s & " could not be found" & vbCrLf & "I'am going on break"
        Exit Sub
    End If
    Range(r, r.Offset(0, 1)).Clear
End Sub

Upvotes: 2

Related Questions