ranges

Does anyone know if it is possible to prevent users from clicking on File -> Properties-> Advanced Properties? I have written code to hide named ranges, so don't want them "revealed" via the above method. Thanks

Hi! I'd need some ideas in troubleshooting SUMIFS formula that returns #VALUE ! error I have a named ranges (within a table) to check particular "Department" + named range to check particular "Cost_type" and summarize the match. So the formula looks something like...

Hi All, I have inadvertently, through another piece of code, got a number of files that have 2 named ranges with the same name. I'd like to delete just one of these but not sure how i'd write the code to ensure I was deleting the correct one (the bottom of the 2 below): Name...

I was wondering if anyone could direct me to the proper code for this, as this is really tedious. Basically, I have 7 worksheets in a workbook I am updating. Once all the calculations are made, I then need to copy certain ranges of these worksheets to files/workbooks (as values) that go by the...

Hey guys, I'm trying to dodge making cycles to fill up my array, so i was hoping to just do this: lastRow = Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row Set bigRange = Application.Union(Range(Cells(2, 3), Cells(lastRow, 3)), Range(Cells(2, 4), Cells(lastRow...

Hi, I've created several different Macros for Filtering a spreadsheet for defined values (SKUs) but occasionally these values change. I was wondering is possible to create a macro in VBA that filters a spreadsheet based on all the values in a column on a different sheet in a different file? I...

Hi all - I have a worksheet that contains about 20 WORKSHEET scoped named ranges. Normally, when I delete a sheet with worksheet level named ranges, the named ranges are automatically removed. When I delete this particular sheet - the named ranges are still there - but they are turned into...

Hi People of the Forum, First of all, this is more of a "I wonder if..." question rather than a I REALLY need this to be solved situation. Anyway, I have a table in which I need to name each row as a range, not a problem, I use: For r= 2 to 71 .Cells(r,2).Resize(1,28).Name="Week"&r-1...

Hi Sometimes the obvious is obscured. Is it possible to make the NAME of a range dynamically linked to a cell such that the range NAME will change if the referenced cell is changed? I am OK with making ranges dynamic but never made the name so? Cheer Paul

Hi Excelakos, I've started a new thread so we don't continue to hijack the previous thread with a different problem. Here is the same solution across sheets, just change the names & ranges to what you need and test if it suits; Book1ABCD1Service CodeRate Valid From (dd/mm/yy)Rate Valid to...

I have a data field between C8:j350, need to sort on C, then G, then I on different selected ranges when selecting fields between C and J any help will be appreciated

hi when using named ranges in vba....i found that you dont necessarily need to refer to the worksheet e.g let's say i have named range "desk" in the tab "A" usually in VBA i can just do range("desk").copy but for this one particular file, it doesn't work...i actually need to do this...

Hello, I am trying to get records placed into two different columns depending on each grouping of ranges on dn1 and Ext in table example below: If dn1/ext is asingle range then needs to be separated with a comma ',", else is in subsequent ranges then separation with a dash "-" Results would...

I have a complicated UDF that takes several ranges as parameters. Each of these ranges can have up to 30-40 values. Each of these values must conform to certain limits, which vary depending on the values in the corresponding cells in the other ranges. The UDF does extensive validity checking to...

Hi there I'm on the last bit (finally) with this project. I now need to: Save as new workbook Name should be: [Country]_Move_Template_[todaysdate] Country would equal a named reference on Worksheets("Instructions").Range("Table13[Country]").Value the user would need to choose the...

I have an array of Date Ranges (Beginning and End Dates in two columns); I need to determine in which "week" a specified resides, and display the Amount (which is held in a third column. I have this formula to do so: =IF(AND(B11 <G36 >F36),K36,0) It works fine for one row in the array of...

Hi I have looked at this thread which was helpful but doesn't give me quite what I need.. https://www.mrexcel.com/forum/excel-questions/1002856-vba-clearing-specific-table-columns.html I also found this useful and started to write the code but realised it was referencing rows and I need...

Hi We've had a weird problem where excel seems to create hundreds of random named ranges in files which have NO named ranges. It's only happened three times and all of those times it happened on files which were linked to other files but it also doesn't always happen so I am not sure what's...

So i have a question about "allow edit ranges". I Just learned how to use it but i wanted to know if there is a way to paste information in the cell. To be more clear, I have a B:B under the allow edit ranges as well as L:L. But I have others copied information and pasting in the same workbook...

Basically I am trying to build a cheesy slot machine simulator lol. I am having trouble figuring out the code that will compare the range ("B2:D2") to("B3:D3") and then compare ("B3:D3") to ("B4:D4") and finally ("B2:D2") to ("B4:D4"). If any are the same then they win. I have tried: dim...