I have worksheet that contains the wording "Total For Page" in columns A:N. I would like to find that wording delete that row and 3 rows below it. found the code below that works, but I have to continually run it to find the wording and delete the rows. The code doesn't search all and delete in one shot.
[Code]....
Code below. I need it to NOT run if the sheet week2 doesn't exist. Currently it gives a runtime error '9' out of range. This is due tot he sheet not being present because sometimes it is not generated.
Code:
Sub RemoveColWeek2sheet()
Dim ColNo As Integer
Dim rng As Range
Set rng = ThisWorkbook.Sheets("Week2").UsedRange
[Code] .........
I have a workbook with one sheet (called "Pipeline") and another sheet called "Completed". The Pipeline sheet is used to keep track of all of the tasks that the team are working on, and then when the task is completed, it needs to be moved to the "Completed tab".
I currently have managed to write some code that looks for items marked "completed" in row G of the "Pipeline" sheet, then copies them over to the "Completed" tab, and then deletes the row from the "Pipeline" sheet.
There is one macro to copy the completed cells (called "Transfer"), and then another to delete the cells (called "Delete"). I then just have macro called "Clean" that just calls the transfer macro, and then calls the delete macro.
Often, there are 6 tasks marked complete, and only some of them will be copied over, but all of them will be deleted (a disaster).
Transfer Macro : [Code]....
Delete Macro : [Code]....
Overall Macro : [Code]....
My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.
View 14 Replies View RelatedI am really new to using Excel macros and having an issue trying to insert data in a column where the data may or may not already exist. In Column A I have a list of product lines.
Prodline1
Prodline2
Prodline3
Prodline4
Prodline5
I want to search through this list and if Prodline1 does not exist, then add a Prodline1 row to the end of the list, then check for Prodline2, Prodline3 etc and do the same. On any given month I may or may not have data for the Prodline but I still need to see it in my list. I've tried this code below but only get data if I define an actual cell and it only works for the first one.
Set R = ActiveSheet.Range("A1")
endrange = Range("A65000").End(xlUp).Row
For i = 1 To endrange
[Code]....
Working at a company where we every month extract our customers into a excel document.
This is the CRM system , and some of the clients can't (shouldn't) be approached.
In Cell C is their customer classifications. In total there is about 60 different classifications. 10 of them is clients who we can send information to. For example customers with class 11111 or 41414 or 51515 or 61616 (among with 45 others) can't be approached. I'd like a macro/script who can delete all the nonrelevant clients so that afterwards I have a list of clients who can be approached.
See the excel sheet [URL]
I need to delete all the digits before / and also the / - in the valuta columns. But this =RIGHT(B2,LEN(B2)-6) doesnt work It says > the formula you typed contains an error
I'd like to tell Excel that anytime it finds the text "ABC" anywhere in any cell in column B but doesn't have any data in column A, to delete the entire row.
View 6 Replies View RelatedIn the attached sheet, i would want to delete the text "ABC" because it is not contained anywhere in column A.
Couldnt find a macro on here for this...
I have a macro below where it looks at sheets name and deletes if Jan is not there.
I would like to change this a bit so that it looks for all 12 months (Jan to Dec) instead of looking for Jan only.
Sub DeleteRowsBasedOnCriteria()
'Assumes the list has a heading.
Dim cl As Range
For Each cl In Range("A6", Range("A65536").End(xlUp))
If cl.Value <> "=~*" Then
cl.EntireRow.Delete
End If
Next cl
End Sub
but it is deleting every row, I am not sure what is wrong?
starting in cell A6
if cell does not start with *, then
delete entire row,
next cell
that is the logic im seeing...ive tried a few o ther combinations like "~*" etc...
I have a column of cells (Column I2:I1063) with zip codes in it and I want to keep the row if the zip code matches one on the list in a column B2:B100 on a separate sheet (Sheet1).
View 4 Replies View RelatedI have some code (probably a little inefficient, but still) that should delete any rows that contain nothing in column V. My problem is that it only deletes 1 row at a time:
Dim c As Long
Dim Limit As Long
Limit = Cells(Rows.Count, 11).End(xlUp).Row
For c = 2 To Limit
If Cells(c, 22).Value = "" Then
Cells(c, 22).EntireRow.Delete xlUp
End If
Next c
In my first workbook I select an area to copy/paste into a new wb with this code (from the board)
[Set source = Range("A55:K109").SpecialCells(xlCellTypeVisible)
Dim FName As String
Dim VBComp As VBComponent
Dim wbnew As Workbook
Dim x As String
Set wbnew = Workbooks.Add
Set dest1 = ActiveWorkbook
dest2.Activate
With ActiveWorkbook
FName = .Path & "code.txt"
If Dir(FName) "" Then
Kill FName
End If
For Each VBComp In .VBProject.VBComponents
If VBComp.Type vbext_ct_Document Then
VBComp.Export FName
wbnew.VBProject.VBComponents.Import FName
Kill FName
ElseIf VBComp.Name = "ThisWorkbook" Then
If VBComp.CodeModule.CountOfLines > 0 Then
wbnew.VBProject.VBComponents
The complete code in this workbook sends the entire workbook to one person and it creates a new workbook and pastes a range and the VB code needed to run the button it copies and sends this to another person.
The codes runs excellent in 2003. In 2007 I can get the entire workbook to send to the first person, but
The email to the second person will send, but they can not open the workbook. I get the error "File Format or file extension is not valid. Please verify file is not corrput and that extension matches file.
I know trust access to visual basic has to be enabled in 2003 for this code to run, so in 2007 I went to the Excel options trust center and clicked the box which says Trust access to the VBA project object model.
When I step through this code everything looks great. The worksheet area is correct, the button is copied, the code can be seen in the properties window, but then I get the email and it will not open.
There is even a line of code that kills the file after the mail is sent (temporary saving location is the desktop). So, I used a ' and made it a comment line so I could try to open the file from my desktop. It will not open there either. File extension being used is .xlsm
I had to remove a lot of pictures in the xls file in order to attach it, (since the original is about 5.3mb) but it worked out. When you move your arrow over the titles in column A a picture shows up thanks to a code. When you change the status in column G, the color changes together with the title in column A, as it should.
View 4 Replies View RelatedMy goal is to learn how to universally add specific codes to all clients in one shot.
Here's an example of the data I'm working with......
In column A - I currently have a list of 200 unique clients listed multiple times (1000 rows).
In column B - I have several different types of codes, but that are the same for each client.
Example:
A B
Client 1 Code A
Client 1 Code B
Client 1 Code C
Client 2 Code A
Client 2 Code B
Client 2 Code C
Etc. Etc. for 200 clients
So the question is: When adding a new code, How do I add a codes to all clients in one shot?
I have an formula if statement that returns "deletethisrow" if the test is true.
For every occurence of "deletethisrow" I want to delete the row. The number instances will be variable each time I run the file. So maybe it will find that string, maybe it will find 10 instances. I want to do some kind of loop that won't error out when it cannot find "deletethisrow", but will delete the rows for each instance where it does find this string.
I know it was verbose, but if I just do a loop for a fixed number of loops it will error out if it runs out of rows to delete.
My company recently upgraded everyone to Microsoft 2010 from 2007 version. I have no substantial VBA skill and left with a VBA code which is supposed to extract a list of outlook emails sitting in a shared mailbox into Excel.
I was using that VBA code in Outlook 2007 and it worked fine but shows the following error when run in Outlook 2010: 'Run-time error '-2147221233 (8004010f)': The attempted operation failed. An object could not be found. Here is part of the code:
[Code] .....
It worked after one of the members suggested to "click on any line of this code and press F8 repeatedly until the yellow focus moves to the error line, don't press F8 anymore. Now in immediate window, copy paste each of below lines, press enter after each line. Let us know where the error occur." However, it stop working the next day.
? olNS.Folders("Mailbox - Market Intelligence").Folders.Count
? olNS.Folders("Mailbox - Market Intelligence").Folders("Inbox").Items.Count
? olNS.Folders("Mailbox - Market Intelligence").Folders("Inbox").Folders("MI").Items.Count
The code below works correctly on certain sheets. The code is supposed to loop through worksheets in an array, calculate the percent change from 1990 to 2012 and from 2005 to 2012, and put the calculations on the 4th and 5th row from the last non-empty row, respectively. All the sheets are identical except for 3. The sheets that are different only have a different number of years of data. For some reason this causes the macro to put the percent change calculations in random rows below the correct location. Also, the macro doesn't work correctly on one of the identical sheets.
[Code] ......
Example of how the macro runs correctly on an identical worksheet : correct.gif
Example of how the macro runs incorrectly on one of the 3 sheets that are not identical : incorrect.gif
I asked for a macro to delete the whole row if a duplicate customer number was found in column B. Sometimes, though, my column numbers change. So, logically thinking, I simply changed the criteria, but the macro ONLY seems to work if duplicate customer numbers are in column B only.
This code below won't work if the Customer Number is in column D instead of B even if the reference of B:B is changed to D:D, it doesn't carry the macro over.?
Need to import multiple text files in one shot. (Excel only allows 1 at a time)
Most file names are similar just numbered. ex. JoeBlowfile 1.txt, JoeBlowfile 2.txt, JoeBlowfile 3.txt, etc......
All text files have 30 lines of text
For example: JoeBlowfile 1.txt will start at A1 and end at A30, then JoeBlowfile 2.txt starts at A31 and ends at A60 and so on.....
May need to import 1 to 10000 files at a time.
all in 1 row single line starting at A1
once all files are imported need to export to 1 single text file file saved in XXXX folder with XXXX.txt name.
Any way to create individual excel files for over 300 people in one shot.
I have created an excel template and have a list of 300 people. Rather than one at a time, is there a way to do it in one shot?
I'm having this problem where I used the camera tool to take a shot of some range of cells. After taking here on the worksheet the image looks fine but when I print preview the same image seems to be bigger than the actual image.
View 4 Replies View RelatedMy problem is how do i get multiple column values at one shot.
For example in one excel sheet i have columns A,B,C,D,E and in A column i have all the Partner ID's and rest of the columns i have the data.
Now in other excel file I have Partner ID's which are not in order...now i want the data in all 5 columns according to partner id's from the previous sheet i need to do a vlookup function for five times to get the same data....is there any way that we can do it in one shot.
I have my code here:
VB:
Sub openfiles()Dim Path As String
Dim ExcelFile As String
' Path = GetFolder("C:UsersKinteshDesktop")
Path = "C:UsersKinteshDesktopVBA programmingMaps"
ExcelFile = Dir(Path & "*.xls")
[Code] ....
NextCode:
GetFolder = sitem
Set fldr = Nothing
End Function
My problem is that the code all actually works (including the function and when I use the commented part), but pointing to this one specific directory (the one I'm using right now), literally nothing happens.
The title in the attached chart is a camera shot transferred from another excel 2007 worksheet in the same workbook.
I'd like to remove the border line, I've tried right clicking... then format picture ...no dialogue box comes up. I've scrolled through all the commands in the ribbon + non ribbon commands and can't find anything that seems appropriate that's not greyed out ....
border line.docx
I have a sheet in Excel 2004 for Mac that is a patient list with over 2000 patients. I need a rule that will compare values (patient IDs and responsible party ID) in two adjacent cells, if the values are different, to delete the entire row. The goal is to get rid of patients (rows) that are not the responsible party (responsible party ID).
View 3 Replies View RelatedI'm trying to write a VBA script which will delete all rows in my Excel spreadsheet where Column I (which contains a status code) does not contain the word "Completed".
At the moment, I'm doing this the other way round: my script is able to search for entries in Column I which contain the status codes "Pending", "Awaiting Authorisation", "In Progress" etc and delete them. The idea is that when all those rows are deleted, I'll only be left with rows which have a status of "Completed". This works fine at the moment. However, the concern is that if a brand new status code is added to the data file, my script would be unable to pick it up and delete it. This is a small sample of the code I'm currently using (which deletes all the rows with statuses other than Completed):
I have 4 values in column A (Let's say A1:A4)
These values are also contained in column Q among/with many other values. I am looking for a script that will find these 4 values in column Q and delete them by shifting the cells up. I need something like:
Look A1, If the same value exist in Q column Then delete the same value cell in Q and shift cells up
Look A2, If the same value exist in Q column Then delete the same value cell in Q and shift cells up
Look A3, If the same value exist in Q column Then delete the same value cell in Q and shift cells up
Look A3, If the same value exist in Q column Then delete the same value cell in Q and shift cells up
Here is what Im trying to do: I have a VERY large excel file (15,000+ lines) that has groups of text (this is all in one column)
I want excel to search for the cells with "UID" and delete that row plus every non-blank row above it. (so it deletes the entire "group") Here is example layout of what Im working with. The end result should be only "groups" without the text "UID" following it.
ExampleCell1
ExampleCell2
ExampleCell3
UID = example
ExampleCell1
ExampleCell2
ExampleCell3
ExampleCell1
ExampleCell2
ExampleCell3
UID = example
Now Im thinking this may be impossible, but I've seen some crazy things done with excel macros and was really hoping someone can help me out. Otherwise Im doing this manually for 15,000 lines of text.
