I have a list of values. they are in C2:C25
I want to go dorn column B, starting at B26:B, and if the value in B equals any of the values in C2:C25 than delete that entire row(not just delete the word, because there are values in column C and D that I need gone with the C value).
I ve got a list multuple values. All of them contain 00 before the actual value. How can I delete those two 00.
Here's an example
Currently Desired Outcome
0030148099 30148099
0030148099 30148099
0030148100 30148100
0030148100 30148100
0030148101 30148101
Each day I am going to have a list of about 300 different ID numbers, which i have already got a macro for creating, that outputs them into a single column on a sheet.
The next challenge Ill face now, is that I'll have a list of maybe 500 ID numbers in another spreadsheet.... I need a way to basically tell excel to keep rows that contain the numbers in 1 column on the list of 500, that correspond with those on the list of 300.
Numbers that are not found on the list of 300 ID numbers, must be deleted, along with the entire row.
I essentially need a macro that runs something like this that i found online, but instead of it just looking for the word "apple" as rows to delete, i would need it to check to see if the number is one of the 300 on my list.... and delete the row if it is not on the list
PHP Code:
Sub Delete_Rows()Dim rng As Range, cell As Range, del As RangeSet rng = Intersect(Range("A1:C20"),
ActiveSheet.UsedRange)For Each cell In rngIf (cell.Value) = "Apple" _ThenIf del Is Nothing ThenSet del = cell
Else: Set del = Union(del, cell)End IfEnd IfNext cellOn Error Resume Nextdel.EntireRow.DeleteEnd Sub
I have 2 columns in Excel wich havn't the same amount of lines.
One colom is 65.000 lines and the other one is 64.000
I am looking for a option to delete 1 line after each 65 lines.
Is this possible?
I'm trying to remove a proceedure code or just line 3 if I could in the active workbook"ThisWorkbook" mod. However I'm having an issue with this code. how to remove all the code that is in "ThisWorkbook" mod? or just line 3 in that code. I have other mod that I want to keep. So I dont want to wipe out all the code from the workbook. Here is what I have but I get a sub or function not defined error.
View 2 Replies View RelatedI am attempting to write a macro that given a range of cells goes into each cell and deletes the text after the first bullet. An example of this would be:
The original cell contains: ....
I have 3 different sheets named:
USA
UK
DK
and 1 sheet named:
I would like to have a macro written that when runned will delete all data in line 2 to 1000 in the first three sheets and line 2 through 1500 in the compilation sheets
I have a complex worksheet that I have some duplicates that I want to merge the data together with Daily. The problem is now the process is getting too long due to the data size increasing. Access will not be a feasible solution at this time.
Column A would have the matching info.
The cells I want to copy are in columns AM:AP
Way it looks now:
A, [Col B:AL], AM, AN, AO, AP
Product#, [B:AL], New List, ID, Vendor, Rep
07256,[Data I want to leave],,,,
07256,[Blanks],HighRisk,32,59,1732
Way I want it to look:
A, [Col B:AL], AM, AN, AO, AP
Product#, [B:AL], New List, ID, Vendor, Rep
07256,[Data I want to leave],HighRisk,32,59,1732
Delete the second row after the data was merged and look for remaining matchs to do the same.
After it is completed have a message box indicating the number of records that this was done to.
i'm trying to tweak Chip's code [url] to include the body of the module as well.
it works, but when there's blank lines in-between modules it'd show less of the modules.
is there a way to delete the empty line in VBE?
or did i miss something out in the code below?
Sub ListProcedures()
Dim VBCodeMod As CodeModule
Dim StartLine As Long
Dim Msg As String
Dim ProcName As String
Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("MyModule").CodeModule
With VBCodeMod
TTHBF251863100
147943F00KNQ TTHBF251863100
REF VINS DU MONDE SA
O/RTT HBF251863100
I need a macro to transform my multiple lines cell as above ( 5 lines ) into a one line cell as below.
REF VINS DU MONDE SA
As the cells needing changing will always be different it would be good if I could have a macro working on the selected cells only, not a specified range.
I'm writing a macro in excel that edits a text file runs another program that uses this text file as its input and then stores the results. Perameters are then changed in the input file and it is then repeted over and over again. One of the parts I am stuck on is I need to delete the last 5 lines in the input file called INPfile.txt. See below:............
This section is always at the end of the file, is 5 lines long and always starts with [DEMANDS] and always ends with [END].
I'm trying to apply an autofilter to do the ol' "delete visible lines that meet criteria" trick. Unfortunately my vb macro, although applying the string correctly, hides all of the lines...
This is the part of code relevant to filtering (i'm trying to filter on column M which is 13):
I have tabulated data from columns A to I, in column B words different only in last letter, going from A to C,
column F=Date and column G=Hour, something like this: ...
I have 2 lists of codes with values tied to them, columns A & B, and columns C & D. Column A is a list of the codes in use, and column C is every code possible, I need to line up the codes and shorten column C to match column A, while retaining the value that is tied to each code so that I can compare Columns B & D easily.
View 2 Replies View RelatedI have a public/shared Excel 2007 workbook on our network and someone colored an entire column yellow. When that happened, it increased the number of rows to over 655,000. I didn't know that was posible but they are there... The thing moves very slowly now and when I try to delete (45,000 lines at a time), I either get a error window telling me I don't have resources to perform the operation, or I do smaller chucks and after 30 minutes deleting lines, it will not save.
I would rather not have to recreate the whole workbook if I can delete the lines on this one page.
I have around 50 text files with similar design per attached file. I need to import the text files with criteria below:-
1. include file name
2. let user choose the folder
3. exclude data from "work in process summary" to "work in process cost totals"
4. only have one title in the excel files which all text files is combined "Item, Line ....."
5. If the text files do not have title like "Item, Line, ..." do not import
Is it possible to have all criteria listed above by running a macro?
A.txt
If you choose entry number 1, Lines 10 to 14 are hidden. If you choose entry number 2, Lines 15 to 29 are hidden and lines 10 to 14 are unhidden. If you choose entry number 3, Lines 20 to 25 are hidden and lines 10 to 29 are unhidden
Until now i have the following:
[Private Sub Worksheet_Change(ByVal Target As Range)
Rows("10:25").Select
Selection.EntireRow.Hidden = False
If Range("A1") = "1" Then
Rows("10:14").Select
Selection.EntireRow.Hidden = True
ElseIf Range("A1") = "2" Then
Rows("15:19").Select
Selection.EntireRow.Hidden = True
ElseIf Range("A1") = "3" Then
Rows("20:24").Select
Selection.EntireRow.Hidden = True
End If
End Sub]
Entering a code in a cell, I'd like the cell next to it to return a list of possibles which the user can then select:
Line Code Material
123456 Apple
Orange
Strawberry
By entering the line code the user get the 3 Materials that has this Line code and they coose the one they want.
The list I was using to look up looks thus:
Line Code Material
123456 Apple
123456 Orange
123456 Strawberry
I've read a bit about combo boxes and lost myself complete, so please use small words for me.
I built a searchable drop down list in excel which will create quick receipts from a huge list of products.
I entered my products in one column and their respective content and price in the columns next to it.
My goal is to create a searchable drop down list which will include all of my products so that when I search and select the product my customer ordered it will ultimately pull the content and price with it to form a receipt with multiple products.
The problem I ran into is that my searchable drop down list cannot be copied to the multiple rows below.
Cell B8 is active.
EFS receipt drop down.xlsx
I am trying to report on race with splits that involve hundreds of secs, secs and minutes. I wish to rank individual splits and overall. Problem is I can't put in values along the lines of 3:09:87, it comes up with something odd?!
Have attached file that i'm thinking of using
However I only put in a small price list when i tried extending it the workbook produced error as per attached. I thought i would just need to amend the row numbers but it doesnt seem to work
unable to upload at present but prevoius is available on this thread. http://www.excelforum.com/excel-work...hoice-sum.html. Need to extend price list to cover up to 10,000 lines
I receive a monthly report containing a list of people, and how much is being paid for certain services. The company that sends me this list is preparing to adjust their rates and it will be retroactive back a few months.
The way they plan on doing it is by means of taking a credit back several months, then "re-paying" the correct rate. The main data will include the month for which the payment (or credit) is being made, the person's unique identifier, as well as the amount.
Here's a sample of what it would look like:
Name, ID, Month, Amount
John Doe, 123, 04012008, 25.00
John Doe, 123, 03012008, -20.00
John Doe, 123, 03012008, 25.00
John Doe, 123, 02012008, -20.00
John Doe, 123, 02012008, 25.00
So basically the above shows they paid $25.00 (correct rate) for April 08, then they took back $20.00 the prior two months (the old rate) and paid the correct rate right afterwards.
In what I need to do, this is going to be a lot of work. Is there a way to programatically merge the amounts given the member's unique ID as the "key field" as well as the same month? So it might look like the following:
Name, ID, Month, Amount
John Doe, 123, 04012008, 25.00
John Doe, 123, 03012008, 5.00
John Doe, 123, 02012008, 5.00
Just giving the sum of the amounts for a the given people in the same month?
I'm pretty good with VBA but this one is stumping me.
I have a spreadsheet where I am tracking several entries in a table that will keep growing. Three fields are Data Validation Drop Down Lists. The macro below works well to clear the two lists to the right when the first one is changed by the user.
[Code] .....
I want this to affect the rows below it in the table as they are added.
I have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.
View 5 Replies View RelatedI'm currently working with 3 dropdown lists depending on each other using data validation and =indirect(). I arranged them like in a top-down approach where you have a mother-list and child-lists (categories-->subCategories-->subCategoryCriteria). The user should easily chose first a category then a subCategory etc... This part works perfectly
My question is: when a user chose in the dropdown list e.g. a category, a subCategory and a subCategoryCriteria and he now maually deletes the value in the highest level, which is the Category (mother-list), all sub level values like subCategory and subCategoryCriteria will stay. It shouldn't be that way, because it's not logical. If you turn a tree upside-down (top-down approach) and cut one of the higher positioned trunks, all lower positioned trunks will fall off as well, right? So, my question is: can I use a conditional formatting or a formula to delete or fade out the value in the lower lists when the higher list value is being deleted?
I tried to use If-formula in combination with indirect in data validation, like =IF(A1="";("");INDIRECT(B1))...just and example.
The value doesnt need to be deleted, it could also be automatically face out like white on white or something. It's just confusing for the user if he deletes the highest choice and the lower choices still stay.
I am working on an email marketing project and i have a small problem. I have two different email list. One (List A) is a large list of potential leads. The other (List B) is a list of leads we are not supposed to market to. I need to delete every lead on List A whose email address is also in List B, so that we do not send unwanted emails to our clients.
The best way i have to do this so far is to go through line by line, which is very impractical.
In case it matters here is out list format. Each list has 10,000 + leads. Each lead occupies a row. The row stretches across 13 columns and each column holds a different variable about the lead (names, state, email address).
In my userform I have a list of check box's that can be selected. Currently if more than one is selected, they will appear in the spread sheet in the same line one after another with a space between them. How do I make it so they either appear with a comma appearing after each, so the next value appears on the next line below or most perferably the cell turning to a drop down list with the values?
Current code
VB:
If CheckBox1.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox1.Caption
If CheckBox2.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox2.Caption
If CheckBox3.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox3.Caption
If CheckBox4.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox4.Caption
If CheckBox5.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox5.Caption
If CheckBox6.Value = True Then ws.Cells(iRow, 2).Value = ws.Cells(iRow, 2).Value & " " & CheckBox6.Caption
Spreadsheet current cell appearence if all 6 are selected ' Chinnook EH101 Lynx Puma Sea King Fixed Wing'
Required appearence:
Chinnook
EH101
Lynx
Puma
Sea King
Fixed Wing
I'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.
Insert > Name > Create
Name: Fruit
Refers to:
banana,apple,orange
When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.
The function below should return the value in a cell IF the string in the cell two cells to the left of it is "Nemo" Omitting the part highlighted in blue, my function returns a list of unique values...but when it's included, nothing is returned at all. Perhaps, there is an error in the syntax that I'm overlooking?
Function listUnique(rng As Range) As Variant
Dim row As Range
Dim elements() As String
[Code]....
how to list values from multiple columns in a dropdown list based on lookup value of 1st column as below.
This is how the table looks like.
Product MOLD1 MOLD2 MOLD3
4" AB1 AB2 AB3
6" ZA2 zd4 -
This is how the dropdown list should look like for Product 4"
ab1
ab2
ab3