Dynamic Range Formula or Non Contiguous Single Column Based on Value of 2nd Column?

[drew.j.harrison]

After trying a few options and searching for the last couple hours can't seem to find a way to have a dynamic range that will select the cells in Column B if the corresponding value in Column A is equal to a specific value ("Y"). Any info on how I could accomplish this is appreciated. FYI, I am generating the dynamic range specifically to identify cells to clear the values from in a macro. Thanks.

Cross Post on ExcelForum

 

[drew.j.harrison]

Quick clarification, I am actually looking to find a way to define dyanmic NAMED range. Sorry for any confusion.

 

[Fluff]

Why not just do it all in the macro

Sub drew_j_harrison()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Replace "Y", "=xxxY", xlWhole, , False, , False, False
      .SpecialCells(xlFormulas, xlErrors).Offset(, 1).Value = ""
      .Replace "=xxxY", "Y", xlWhole, , False, , False, False
   End With
End Sub

 

[drew.j.harrison]

I suppose I could use a macro to do it all but was leaning toward a dynamic named range if possible to reduce the risk would have to modify the macro if I modify the table layout later. I tried the macro above but get an error 400 for some reason.

From what I can tell that macro is supposed to replace all the "Y" in column A with an "xxxY", then replaces all of the cells offset 1 to those cells (column B) with "", then goes back to column A and replaces the "xxxY" with "Y".

If that is the case then that would not work either as the "Y" is actually the result of a formula in column A's cells.

 

[Fluff]

Unfortunately I do not know how to create a dynamic named range along those lines.