Formula for Named Range in Excel - Dynamic Range

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,248
Office Version
  1. 2016
Platform
  1. Windows
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
Excel Formula:
=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
AB
1Description
2Apples
3Apples
4Apples
5Total Apples
6Bananas
7Bananas
8Total Bananas
Sheet1
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,908
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,248
Office Version
  1. 2016
Platform
  1. Windows
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

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

Well-known Member
Joined
Dec 30, 2008
Messages
13,908
Office Version
  1. 365
Platform
  1. Windows
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)
VBA Code:
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,
VBA Code:
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.
 

Forum statistics

Threads
1,144,369
Messages
5,723,945
Members
422,527
Latest member
TotalBeginner201

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top