I'm using Excel 2010). I have column I, which is the sum of columns A and B (first and last names of patrons), and I have used conditional formatting to identify all duplicates in column I with red text. I would now like a macro to find red text in column I and then delete the corresponding rows.
I've been looking around on the web for a while and have cobbled together this code stolen from other people's macros:
When I run it, I get the following error:
Run-time error '1004':Application-defined or object-defined error
Excel doc is called "stupid mailing list.xlsm", sheet I want to run the macro on is called "voters".
I'm looking for one function for each of these properties that will provide them with negligible overhead in all or almost all spreadsheet data cases, such as the normal spreadsheets, autofiltered data, large workbooks, etc.
By the way, by "first row", I mean the first row after the header row, given that the header row is in row 1.
If possible, I'd like to be able to catch & handle the event of a User deleting a row (or potentially a column) from a specific Worksheet so I can then implement some automated "housekeeping processing" on other existing rows / columns.
I'm using XL 2010 so from what I've found so far it looks like I should possibly be using the Worksheet_Change() function, but beyond that I'm currently stuck.
I have an excel workbook where some sheets have a column called "Name" in Row 3. The column where "Name" appears shifts based upon other criteria so it's not set within 1 specific column.
The real data for the "Name" column starts in Row 5. Is it possible to create a macro that looks in Row 3 for "Name", then once it finds that column, it does a find and replace from Row 5 to the end of the data and replaces every space with a ^.
For example, if I have:
Row 3 Name Row 4 Row 5 John Smith Row 6 Jane Doe Row 7 John Doe Row 8 Jane Smith
Just need to delete some hyperlinks in column A on 50+ worksheets. Thought a loop through all the worksheets would do it. Only works on active sheet. Forgive my ignorance, don't really even know where it goes, once it works - module or workbook?
I'm looking to loop through a worksheet with over 1000 columns and would like to delete columns containing a percent sign (%) in the header row which is row 4. Column range to search would begin at column 12.
I'm using a query that dumps information into Excel. The query automatically creates a formula in each each cell. For instance if the first value in the query showed FUNSTUFF, after dumping it into Excel, the contents of the cell would show ="FUNSTUFF". I can just use a find and replace to get rid of the leading =", but sometimes the values of the cells are supposed to have a quotation mark, so using the same method to remove the trailing quote won't work. I need a way to search each cell, check if the last character in the string is a quote, and if it is, delete it.
I have an Excel sheet with lots of shortcuts in the first column. It's the entire shortcut, including the .lnk extension.
What I need to do is to be able to parse out the string to make the shortcut human readable when I create the hyperlink. I'm saving the output as HTML for a user to open these shortcuts. It's on an internal LAN & secure. Using various versions of Excel (2010, 2007 & 2003, predominately the first). Trying to make an ugly, 140+ character shortcut into a more readable form for web navigation purposes.
Up to a point, the shortcuts are similar: a123meworkfolder1folder2folder3shortcut1.lnk a123meworkfolder1folder4folder5shortcut1.lnk a123meworkfolder1folder6folder7shortcut1.lnk a123meworkfolder1folder8folder9shortcut1.lnk
The strings are the same up to the 7th occurrence of the "" character, but after that, the foldernames start to change & the shortcut may be down a level (an extra folder) or more. Also, the lengths of the strings change as well, so I can only do positional parsing up to a point.
I have a simple problem. I have many worksheets which all contain data in the first four columns. I want to be able to create a chart (a scatter chart) in the first worksheet which plots the data on that worksheet and then format it how I want it to look. This I can do.
Next, I want to copy and paste that graph into all the other worksheets, but have the pasted graphs display the data in that worksheet.
As it stands, in Excel 2010, all the pasted graphs keep showing the data in the original worksheet irrespective of which worksheet I now have them in. This seems to be different to older versions of Excel and is very annoying and has to be changed by hand which is very time consuming.
How can I make charts that don't have the worksheet name in the cell reference, or how can I paste a chart so that is updates the references to the same row and column but in the new worksheet?
I know I could copy and paste the entire worksheet and then manually copy the new data into that copy of the worksheet - but that is not a real solution to this problem.
I need to remove numbers from a string of text and put them into new cells in Excel Starter 2010. There are two different values which need moving. The first is in parentheses and the second follows the parentheses.
Here's a few examples of what I mean:
Original text RED008 - Wickaman and Hoodlum bandwidth (0.06Gb @ 2.00) 0.12 MFR005EP - Various Artists bandwidth (0.19Gb @ 2.00) 0.38 RAZORS010 Future Cut bandwidth (0.01Gb @ 2.00) 0.01
would like to split into...
Cell 1 RED008 - Wickaman and Hoodlum bandwidth MFR005EP - Various Artists bandwidth RAZORS010 Future Cut bandwidth
I have fixed headers on row 16, from columns A-AC.
I want to be able to delete the entire column, if the row has a certain string, such as "Chart ID" .
I also want to expand it to include other strings such as "Month" and "Source" . So if it contains any of these words, the columns should be deleted. It should be an exact match (as other headers contain the word "month").
Workbook contains a number of named ranges where the name is no longer used. I wish to remove the name assigned but not the actual range of referenced cells. When using Name Manager to try and delete the name the Delete button is greyed out and not available. Workbook is being heavily modified from its original form used by another. Is this possibly from a protected area from prior user? However, I have not had a problem creating new worksheets, cell ranges, etc. Also, even for new ranges I create I am not able to delete the name as the Delete button is greyed. What is going on and how can I delete just the unused range names? Version is Excel 2010.
I deal with a rather large excel database (the range is A1:AV168266) that contains customer information. Some of these various customers are affiliated with a group called "ascend" and I need to be able to filter all my list by every customer affilliated with Ascend. The trouble is that the word "ascend" can appear in 8 different columns and usually when it appears in one column it doesn't appear in the others and their can also be more text in the cell beyond the word "ascend". I want to create a formula that will simply look for the word "ascend" in any cell of a row and return a result I can filter by. I will be putting the formula in column AW and put it in all 170,000 rows of that column, that way I can just filter by a single column.
I've played around with combining COUNTIF with SEARCH and MATCH with SEARCH but nothing seems to work.
I have a list of names (text string) in a column (A) that have associated whole numbers in another column (B). What I would like to do is create a formula that multiplies the text string in cell A2 (for ex.) by the whole number in B2 and return a "list" of the text string in A2 equal to the whole number by which it is multiplied.
Here's a visual example of the results I want to achieve:
Using the above example, I would like to end up with a list in column D with 159 total rows with the text contents corresponding to the whole number in column B.
With VBA in Excel 2010 I am connecting to a SQL server by using ODBC.
This is a two part process:
Part one:I have created a connection string that gets me the following data: A, B, C, D, E from sheet tpoPurchOrder Where B is equal to "1" And Where C is equal to a changing field under Sheets("Macros").Range("B2")
Here is part one
Sub Part1() Sheets("Open PO by Vendor").Select Sheets("Open PO by Vendor").Cells.Clear With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "ODBC;DSN=Connection;Description=Description;UID=USER;PWD=PASSWORD;APP=Microsoft Office 2010;WSID=Workstation;DATABASE=Database" _ , Destination:=Range("$A$1")).QueryTable
Part one works perfectly.
Part two is where I have problems.Part two is a little different because I have the same connection, but what changes is the table that I am looking at "tpoPOLine" instead of "tpoPurchOrder" and the where is now going to have a variable number of commands.
I want part two to be depend on part one. Under part one I want the result from column E to be a where statement in part two.
In particular: *E3 from Part 1*, etc.
Code: "SELECT tpoPOLine.Status, tpoPOLine.POKey, tpoPOLine.ItemKey, tpoPOLine.POLineNo, tpoPOLine.UnitCost, tpoPOLine.ExtAmt" & Chr(13) & "" & Chr(10) & "FROM mas500_DII_app.dbo.tpoPOLine tpoPOLine" & Chr(13) & "" & Chr(10) & "WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (" _ , _ "tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)" & Chr(13) & "" & Chr(10) & "ORDER BY tpoPOLine.POKey" _ )
Now my problem is that sometimes the E column from part one ends up being 1 row, sometimes it ends up being 50 rows. I would like the code to change accordingly.
1. Pull everything from the SQL server filter once in excel. This is not as efficient as the database has ~300,000 rows in the tpoPOline table and would take a lot longer then needed.
2. Create a nested if table and
Code: WHERE (tpoPOLine.POKey=*E2 from Part 1*) OR (tpoPOLine.POKey=*E3 from Part 1* ) OR (tpoPOLine.POKey=*E4 from Part 1*)"
to the value of that nested if table
The nested if table would be something like =if(isblank(E2),"E1",if(isblank(E3)... etc for ~50 rows. I know the syntax is incorrect but you get my point.
3. Rerun the query for each value in column E. That would require the connection to happen ~50 times which would not be that great, as well as I would have to copy and paste the data after each run as the tables cannot overlap.
What I am looking for is a way to run this only once, on one sheet, without writing a nested if table with 50 if's.
I know there are many ways to create an "All Data" worksheet. Copy & Paste is the most obvious or pasting named ranges into the new worksheet. I have a workbook with 48 tabs with up to 1000 rows of data per sheet. I need to merge each tab into one main "All Data" worksheet.
How to combine these 48 sheets in an easier way than the two options I already know (Copy/Past or Paste Named Range). Any Add-In's to Excel that can possibly do this? I am using Excel 2010.
How to make this "All Data" worksheet combining data from each sheet of the 48 tabs would be most useful.