create a Named Range via VBA based on cell content

[carsto]

I need to create a Named Range via VBA

For each cell in Column A, if the value of the cell = "ABCD" then include the entire row in the Named Range

So if A8, A13 & A18 contain "ABCD" then
MyNewNamedRange ='Sheet1'!$8:$8,'Sheet1'!$13:$13,'Sheet1'!$18:$18

Thank you

[Ron Coderre]

There are usually more effective/efficient alternatives to that approach.
How were you hoping to use the named range?

[carsto]

I have to look through every cell in the rows that start with ABCD for a certain Code,
If the Code is found, I copy the cell above and below and the cells in rows 2 & 3 of the same column to Sheet2

this builds a table on Sheet2 containing only the data relevant to the items Coded for what I need, like this:

DataW DataX DataZ DataY from perhaps D8
DataW DataX DataZ DataY from perhaps Z8
DataW DataX DataZ DataY from perhaps BB13
DataW DataX DataZ DataY from perhaps AG18
DataW DataX DataZ DataY from perhaps DH18
DataW DataX DataZ DataY from perhaps IV18

Please Login or Register  to view this content.

I just want to make the search of the rows starting with ABCD a variable so any new rows added will be included.
The Named Range idea seemd easier than embedding a loop inside a loop
For each cel in A:A that = ABCD search every cell in the row for the code.
I always run into trouble when I try to select inside of a selection!

[Ron Coderre]

Perhaps something like this:

Please Login or Register  to view this content.

Is that something you can work with?