Excel difficult Challenge (dynamic named range to ignore hidden rows or filtered rows)

Hello,

i need help, is there possibility to create named range where it only encompasses the visible cells only?

thanks.
LVL 6
FloraAsked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
After looking at your workbook I'm not sure what you want, because if all you want is for the cells in column E to be equal to what's in column B then all you need is a simple =B2 formula in E2 and copy down. However if you want to do it via VBA then this is one way. Add it to the sheet's code.
Private Sub Worksheet_Calculate()

Application.EnableEvents = False

Dim r As Range
For Each r In Range("B1:B" & UsedRange.Rows.Count).SpecialCells(xlCellTypeVisible)
    r.Offset(0, 3) = r
Next
Application.EnableEvents = True
End Sub

Open in new window

0
 
FloraAuthor Commented:
@D Patel

did you try the link before posting it here?

because i found that link by googling prior to post the question and it do not work at all.

see attached file. Column E do not return the visible cells from Column B, so that solution in mrexcel is not working at all.
Book.xlsb
0
 
Martin LissOlder than dirtCommented:
What you want to do isn't possible because a named range must be a range of contiguous cells, but if you describe how you would want to use this non-contiguous named range, then perhaps I could come up with a workaround.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
FloraAuthor Commented:
Thanks Mr. Wolfe for removing Non-compliant answer.
0
 
Martin LissOlder than dirtCommented:
I edited a typo in my last post.
0
 
FloraAuthor Commented:
Dear Martin,
thanks alot for your help as usual.

here is the real senario. please see attached workbook.  there is a formula in column A of sheet "Worksheet Names"  the formula is to list name of all worksheets of thisworkbook in column A.
it is all good and i have created a dynamic range named DynamicRangeSheets to encompass these sheets names in column A.  i pass the refer named ranges in my formulas to return these names as an array to inside my formula. it all works, but  becuase i have some sheets that contains wss and i want to exclude them from the named range hence i used filter in column A but my named range "DynamicRangeSheets"  still returns those hidden rows sheet names that contain wss.  i was wondering if there is a solution that my named range DynamicRangeSheets  excludes and ignores the hidden rows in the filter.  

if you select the named range and press F9 then you get ={"Sheet2";"Sheet3";"wssSheet4";"wssSheet5";"Sheet6";"Sheet7";"Sheet8";"Sheet9";"wwSheet10";"Sheet11";"Sheet12";"Sheet13";"Sheet14";"Sheet15";"Sheet16"}

i want the name range to return all sheets without the ones with wss
so the ideal needed result will be like ={"Sheet2";"Sheet3";"Sheet6";"Sheet7";"Sheet8";"Sheet9";"Sheet11";"Sheet12";"Sheet13";"Sheet14";"Sheet15";"Sheet16"}

this is just an example of the data, my workbook has many sheets that is why i needed a dynamic solution.

please let me know if you have further question and once again thank you very much for your professional help and support. it means the world to me and very much appreciated.
EE.xlsm
0
 
Martin LissOlder than dirtCommented:
Give me an example please of how you would use the (filtered) named range in a formula.
0
 
FloraAuthor Commented:
here is an example of formula.

please see the MyDYNAMICRANGE is holding the array of sheetnames

=SUMPRODUCT(SUMIFS(INDIRECT("'"&MyDYNAMICRANGE&"'!"&SUBSTITUTE(ADDRESS(1,COLUMN(B1),4),"1","")&"$2:"&SUBSTITUTE(ADDRESS(1,COLUMN(B1),4),"1","")&"$40"),INDIRECT("'"&MyDYNAMICRANGE&"'!"&"$A$2:$A$40"),MainTemplate!$A2)*(T(INDIRECT("'"&MyDYNAMICRANGE&"'!"&"$A$1"))=MainTemplate!$B2))

Open in new window

0
 
Martin LissOlder than dirtCommented:
The only named ranges in the workbook you posted are DynamicRangeSheets and Sheets, but that's OK.

Instead of a formula please describe what you would want to do with the dynamic range. What I'm looking for is something like "I'd like to sum up the values in A1 on all the sheets where the sheet name is visible".
0
 
FloraAuthor Commented:
sorry for confusion Martin.

DynamicRangeSheets  is the same as MyDYNAMICRANGE  i renamed DynamicRangeSheets to MyDYNAMICRANGE . as you can see in the example i attached.  i have sheet names in the column A and i put a filter. now the formula posted above do not honor the filtered of dynamic range column A
i need the MyDYNAMICRANGE to return only visible cells of column A inside the formula.

is this possible?
0
 
Martin LissOlder than dirtCommented:
I'm sorry but you didn't answer my question which was...
Instead of a formula please describe what you would want to do with the dynamic range. What I'm looking for is something like "I'd like to sum up the values in A1 on all the sheets where the sheet name is visible".
0
 
FloraAuthor Commented:
Martin, i think i figured it out using your code  of worksheet change event.

thanks alot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.