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

0
votes

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
1
vbaexcel
And? What happens? Is that two separate single quotes? – SJR
I get a compile syntax error on the line starting With Intersect... and yes they are two separate single quotes. It says argument not optional – Porkball21
You perhaps need With Intersect(Sheets("Sheet1").UsedRange, rng.EntireColumn). Might also have problems if your cells contain just two single quotes. Is this imported from elsewhere? – SJR
Hi, not imported from anywhere. I made the change and now get rng.EntireColumn = <Object variable or With block variable not set> error – Porkball21
You need to check that the value is found first. Have you stepped through your code? If you have a choice I would not use single apostrophes as they can be confused by the text prefix. – SJR

1 Answers

1
votes

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