CODEWITHSUNDEEP
vbaexcel
Porkball21
Porkball21

Reputation: 125

Identify column using column header and delete rows where cell contains substring

I am trying to produce some VBA code that will look for my column that contains the column header "New query" in the first cell for row 1. It should then delete all rows where the substring value '' is present in each cell for the column.

This is what I have come up with:

Set rng = Rows("1:1").Find(what:="New query", after:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
With Intersect(Sheets("Sheet1")
.Columns(rng.EntireColumn.Address))
.Replace "''", "#N/A", xlPart
.SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With

Upvotes: 0

Views: 78

Answers (1)

tigeravatar
tigeravatar

Reputation: 26640

You don't want to use Intersect for this, and it's causing the problem. Try this instead:

Set Rng = Rows("1:1").Find(what:="New query", after:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
With Rng.EntireColumn
    .Replace "''", "#N/A", xlPart
    .SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With

Upvotes: 1

Related Questions