I tried a simple for I loop to remove all Named Ranges in a workbook, Unforunately I got a subscript out of Range Error. Is anybody aware of a way this can be done with code?
For i = 1 To 4
ActiveWorkbook.Names(i).Delete
Next i
Any quick way of deleting all named ranges within a given workbook? I can loop over them, but it takes forever. (I've got about 20,000 names in total).
Turning the normal things off doesn't do much good either (e.g. auto-calculating, events, etc)
I am trying to create a macro to search through a workbook and delete "empty" cells, leaving just the range of cells with valid data. So far i've come up empty handed. I have attached a sample workbook with a spreadsheet on it that has the empty cells I speak of. Press CTRL+END to see where it takes you in the sheet and it will be well beyond the range of data I have inserted. The reason I need to do this is because I gets spreadsheets back from clients that need to be imported into SQL and it is very time consuming to have to go through each workbook.
View 2 Replies View RelatedIs it possible for a macro to delete any sheet that within a workbook that does not match a specific list of sheets. For example a workbook will always have Sheet1, Sheet2, Sheet3, Sheet4... but it can have many other sheets added to it. The names of the sheets can be completely different however the sheets that I want to keep will always have the same name.
View 9 Replies View RelatedI am trying to delete all data between two ranges by Macro.
Range one is called Title and range two is called disclaimer.
I simply want to delete all data between them and insert new data ( again between them)
The deleted and inserted will always be of varying sizes.
All data sits in columns "A:H"
I'm looking for a way to delete all named ranges starting with ProfitCenter. The macrorecorder doesn't give me any clues.
View 4 Replies View RelatedI would like to build a Macro to delete units (ex: 10V --->10) of two cell ranges that are not beside each other and eventually automate it to graph these two ranges because I will be running samples and have to produce a graphs for each sample.
I tried the code below, but I can't figure out how to include 2 ranges without deleting letters in other cells in in between the ranges.
for ex: I want to delete the non numerical characters in the two ranges C3:C1000 and H3:H1000 and graph these ranges.
I have macro that brings information from outlook to excel. In the beginning of macro, it deletes range of cells. That destroys the functions that target those cells. Is there a way avoid that? Using some different method or ?
Code:
Dim olApp As Outlook.Application
Dim olNS As Outlook.Namespace
Dim myCalItems As Outlook.Items
Dim ItemstoCheck As Outlook.Items
[Code] ........
Running the macro messes up all funtions that targets those cells.
Like:
Code:
=DATEVALUE(MID(data!#REF!;4;2)&"."&LEFT(data!#REF!;2)&"."&RIGHT(data!#REF!;2))
This really great code to get data from outlook is originally: [URL] ........
i am trying to assign names to my ranges using VBA. i am doing it through the following but seems like it does not work.
Sub MakeName()
ActiveWorkbook.Names.Add Name:="ACCOUNT", RefersTo:="GL_Details!$J$2:INDEX(GL_Details!$J:$J,LastRow_GL)"
End Sub
>Workbook Defined NamesNameRefers To
ACCOUNT=GL_Details!$J$2:INDEX(GL_Details!$J:$J,LastRow_GL)
ACCOUNTING_PERIOD=GL_Details!$C$2:INDEX(GL_Details!$C:$C,LastRow_GL)
BUDGET_YEAR=Expenditure_Details!$BF$2:INDEX(Expenditure_Details!$BF:$BF,LastRow_Exp)
[Code] ........
peice of code which can look down a column, which contains named ranges, and output all the named ranges to a Listbox, and doesnt add the normal text also in the column?
View 9 Replies View RelatedI need my SQL server query to update with names from a range. e.g. pinnog as shown below is a name defined range called Name1 and smithb will be Name 2.
Can someone with me with this code as I am not sur ehow to lookup ranges in VBA.
My code is below ...
I have 2 ranges named MASTER and SUBSET and 1 range named ANSWER. I want to compare ranges MASTER and SUBSET and copy the difference to ANSWER. The ranges have the same number of columns but different number of rows. Sample data is below.
Ideally, I would like a VBA solution executed from a userform button.
Example:
MASTER
aaa apple
aab banana
aac grape
SUBSET
aab banana
ANSWER
aaa apple
aac grape
The combobox is on a userform. This is the code I'm using to populate the Combobox
For Each nName In ThisWorkbook. Names
ComboBox3.AddItem nName.Name
Next nName
I tried to your reply but i get a object error
Can I stop the displaying of the name of the named area when I am zoomed out of a worksheet? I can not see an option on the Tools > Options > View menu.
A workaround, found by accident, is to make the named area have more than one area.
I have two workbooks I am using. Essentially I am copying values out of one workbook, opening a second workbook, manipulating data and pasting it. Everything is working great but one issue! The first workbook has named ranges in it that I don't want in the second workbook (just values). I don't want them because when I try this operation the second time it asks if I want to use the same name or choose a different name. For some reason it is still copying over all the named ranges (all 343 of them!). Is there a way to not allow it to do that or simply just delete the names before I close the second workbook?
Win 7 64, Office 2010
Sub CopytoDB()
Application.ScreenUpdating = False
Worksheets("Setup").Select
Range("A2:A766").Select
Selection.Copy
Workbooks.Open Filename:="D:Server MirrorDatabaseSetup Database.xlsm"
Worksheets("Database").Select
Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
[code].....
I am trying to enter only the named ranges in the active worksheet into an Array.
The amount of named ranges can be from 4 to 7 per sheet.
I'm self taught at VB (This posts on this forum have taught me - thank you!) and have got the below code working on a Workbook - but not on a worksheet level.
ActiveSheet.Names.Count returns nothing, changing to ThisWorkBook.Names.Count returns the sum of all .Names in the workbook.
Sub aTest()
Dim sArray() As String
Dim sJoin As String
Dim y As Long
x = 1
y = ActiveSheet.Names.Count
z = ActiveSheet.Name
I have a long customer listing, names 5 to 36 characters and several with their location in the name as well as a few common duplicate names. I am trying to produce sheet where the customers name once selected opens in the customers spreadsheet and data can be added/amended for sales etc.The lists are not in alphabetical order as when created a customer number is automatically allocated. My aim is just to type in the first letter of the name and the dropdown appears the customer is selected and their card appears. I have tried data validation, lookup, vlookup, Dropdown and Match/find. they only return the first record found and no sign of any others. Find returned all instances of the letter appearing in every name.
View 9 Replies View RelatedCan I create data validation list of the names created in the name box or of the sheet tab names?
View 5 Replies View RelatedCode:
Sheets(Array("Sheet 1", "Sheet 2")).Visible = False
How do I convert the above to using Sheet Codes Names, Sheet1 and Sheet2?
Want to ensure my code will work if the user changes the sheet name.
I have a worksheet with many tables that I use in formulas.
I like tables for a couple reasons, one being the ability to insert/delete rows without affecting the rest of that worksheet row.
Also, automatic copy of formulas/formatting is great.
But, what I really like about tables is the ability to use the naming conventions in formulas.
Problem is when I save this worksheet, after I close it and open it back up, all table references in my formulas have been converted to cell references.
Example:
Code:
=IF((SUMIF('Quote 1'!$M$28:$M$43,">"&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&tblOSSRV[Min Order Cost])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],""&tblOSSRV[Min Parts Per Line])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],"
Two part question:
1) I'm relatively new to arrays, but what I need to do is generate a list of file names and the sheets within each one. I would like to use an array for this, but since I don't have much experience.... well....that's why I'm here. Can someone point me in the right direction?
2) And the second part of this.... I was planning on using the FileSystemObject to determine the files in a selected folder and loop through that list of files, opening each one and harvesting the required info (file name and all sheet names). Should I use the FSO or is there something built into Excel that might be better (and also limit the number of dependencies for this little "project" of mine).
Is it possible to produce a list on a new worksheet of all sheet names and their their internal names within a workbook?
If so I would like the tabbed name's to begin in say A2 with the corresponding internal name in B2.
I am using Excel 2003 and Windows XP.
I have been given a list of my firm’s target clients (in excel) and an opportunities report (exported into excel) from our CRM system, which lists all the opportunities (i.e. opportunities to sell/provide products/services) that have been created for each client. Some of the column headings in the opportunities report are as follows:
Client; Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created etc.
What I need to do is lookup each client, from the target clients listing, in the opportunities report to see whether an opportunity has been created; and if so, return the row of values (i.e. the Opportunity ID; Opportunity Name; Opportunity Description; Created by; Date Created) for that client. The result will be placed next to the name of the client in the target client worksheet.
I have a couple of problems. Initially I tried to use the VLOOKUP function to lookup the client name in the opportunities report and return the Opportunity ID (I then planned to use the same formula to return values from the other columns); however, as the client names in the target client listing were not always written the same way as they were in the opportunities report, the formula often returned #N/A. The formula I used was
=VLOOKUP(A8,'Opportunities Report'!A2:F51,2,FALSE)
So for example, the first client that I was looking up was written as “ABC Ltd” but in the opportunities report it was written as “ABC Limited”.
My second problem was that for some clients, there were multiple opportunities listed in the opportunities report. Where this was the case, there was a separate row (repeating the client name in the first column) for each opportunity created. I think that was messing up my VLOOKUP formula as well.
Is there a way to look up the client name, from the target client listing, in the opportunities report even if it’s slightly different and return the row of values for each opportunity created for that client on a separate row?
I need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.
View 5 Replies View RelatedI have a list of names in a single cell. They are all seperated by a comma, then a space. Example would be: John Smith, Steve Wilson, Wallace O Malley, etc. What formula could I use to pull out the names individually, starting from the farthest right?
View 2 Replies View RelatedI'm trying to sort out a list of names from a website that publishes names in the following format:
DOE John
VAN GOGH Vincent
DA VINCI Leonardo
NADAL PARERA Rafael
JIMENEZ RODRIGUEZ Miguel Angel
What I'd like to do is get the names in the following format
John Doe
Vincent Van Gogh
Leonardo Da Vinci
Rafael Nadal Parera
Miguel Angel Jimenez Rodriguez
Basically all the last names - which are all capitalized - would be moved to the end of the text string. Of course any leading spaces should be removed and I guess using the Proper() function, all capitalized words could be capitalized in a standard way.
I found the following function, here: [URL] ...
but what it does is just take the capitalized words and separate them into a separate cell, which is not all of what I want.
create a script that will replace the names in column A on sheet1 from a Master sheet in the same workbook?
The problem is that different users are entering data on sheet1 col A in different ways example someone may enter Johnc or John C Or John What I want is for something to run down col A on sheet1 and look for the like name on the master sheet if the name matches then do nothing but if the name is like another name on the master sheet then replace the name if they are almost alike.
I need to create a function that selects 4 names randomly from a list of 15 names and displays the 4 randomly selected names in the one cell. Also, you cannot repeat the same name in that cell, (i.e. bob cannot be selected twice in his group of four)
View 1 Replies View RelatedI need to make this macro read FOLDER names instead of FILE names. When I posted this question yesterday to get this macro, I wasn't told that each file in its own folder. I need the folder names now.
_____________________________________
Sub test()
With Application.FileSearch
.NewSearch
.LookIn = "C:Ford"
.SearchSubFolders = False
.Filename = "*.*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = .FoundFiles(i)
Next i
Else
Cells(i, 1) = "No files Found"
End If
End With
End Sub
Here's what I'm trying to do:
In a spreadsheet I have a series of names with associated data, for instance: ...
