Formula for Named Range in Excel - Dynamic Range

[hrayani]

Hello Friends,

If possible, what formula should I use to give the below list a dynamic range
In this case I want the name manger to look at cells (B2:B8) but ignore where the adjacent cell contains word *Total*

If I simply use offset then it picks the entire range B2:B8

=OFFSET(Sheet1!$A$2:$A$8,0,1)

But I want name list to ignore cells B5 & B8

Pls note that the word *Total* can come in any of the cells in the entire range (A2:A8) as its a result of formula

Book2
	A	B
1	Description
2	Apples
3	Apples
4	Apples
5	Total Apples
6	Bananas
7	Bananas
8	Total Bananas
Sheet1

 

[jasonb75]

I will then copy a date from a cell within the sheet & paste in the named range via VBA

Then why not simply identify the range with vba instead of attempting to use a named range in a way that is likely to be problematic and inefficient?

Record a macro whilst you filter the data to show rows that do not begin with "Total", apply the date to the visible rows, clear the filter.

 

[hrayani]

Then why not simply identify the range with vba instead of attempting to use a named range in a way that is likely to be problematic and inefficient?

Yes, this can be done if it's inefficient or problematic.
Do you mean this way using the VBA code

Sub test()
Dim rng As Range
  
For Each rng In Range("A1:A10")

If InStr(1, rng.Value, "Total", vbTextCompare) = 0 Then
    rng.Offset(0, 1).Value = Range("C1").Value
    
    End If
    
    Next rng
End Sub

What I initially though is if the data is big so it's better to go for a named ranged and simply tell the code to look at that range rather than looping for each cell in range...

 

[jasonb75]

What I initially though is if the data is big so it's better to go for a named ranged and simply tell the code to look at that range rather than looping for each cell in range...

The named range (if it is even possible) would need to use arrays with volatile functions. Calculation of that would be inefficient and would be reprocessed after every change to the sheet. The vba will only be processed when you run it.

Do you mean this way using the VBA code

Something like that would work but I actually meant what I said in the second line of my reply.

Record a macro whilst you filter the data to show rows that do not begin with "Total", apply the date to the visible rows, clear the filter.

If you had done that then the macro recorder would give you code something like this (based on example in post 1)

Sub Macro1()
'
' Macro1 Macro
'

'
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$8").AutoFilter Field:=1, Criteria1:="<>Total*", _
        Operator:=xlAnd
    Range("C1").Select
    Selection.Copy
    Range("B2:B7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.ShowAllData
End Sub

which you could clean up a little to get something like this,

Sub Macro1_cleaned_up()
Dim lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row

With Range("A1:A" & lRow)
    .AutoFilter Field:=1, Criteria1:="<>Total*"
    Range("C1").Copy .Offset(, 1)
End With
ActiveSheet.ShowAllData
End Sub

to which you could add things such as manual calculation, screenupdating, etc, in order to improve performance if desired.

 

[hrayani]

Got it….
Thanks