Delete Rows In Txt Input File BEFORE Searching For Data
May 6, 2009
I read in a txt file with the code below. There's some kind of special character in the last 3 lines of the input file that makes the macro crash. I don't need anything in those 3 rows. How can I delete them before my Do Until loop?
-----
Sub Mytxt()
Dim Mytxt As String
Mytxt = Application.GetOpenFilename(FileFilter:="EXCEL files (*.txt),*.txt", Title:="Open the Report file you need")
If Mytxt = "" Then Exit Sub
Workbooks.Open Filename:=Mytxt
Open Mytxt For Input As #1
i = 1
Do Until (EOF(1) = True)
Line Input #1, tempstr
Cells(i, 1) = Mid(tempstr, 23, 5)
Cells(i, 2) = Mid(tempstr, 25, 1)
Cells(i, 3) = Mid(tempstr, 33, 3)
i = i + 1
Loop
Close 1
End Sub
View 9 Replies
ADVERTISEMENT
Jul 21, 2014
I have a huge data file. I would like to have excel automatically delete all rows with the value of 7 in column b. Can I do this without manually selecting all of the rows (I can sort by that value, but there are 120,000 rows).
View 6 Replies
View Related
Jul 4, 2009
I would like a macro that does this. Note for below that the "state" column will never be in a fixed column position, so this macro must search for "state" by name and not column position.
1. Find the column named "state"
2. Bring up a message box saying "Please enter the state/states you would like to use, separate each state with a comma"
3. The user would then enter in each state/states they want to use and press "OK"
4. The macro then deletes ALL ENTIRE ROWS NOT matching the state/states IN THAT COLUMN that were entered in the message box by the user (except the header row of course). To clarify number 4 further, the macro should ONLY look in the "state" column when deciding whether to delete the WHOLE ROW or not.
5. The case should be non case-sensitive, for the "active worksheet", and if possible, should work whether the user enters in more states with either
a) comma state
or
b) comma space state
6. Also, I'm only using "states" for example reasons only but actual text length can more more characters long. The criteria entered in the message box should not be limited to two characters.
View 2 Replies
View Related
Mar 6, 2009
1) Allow user input of a number between 1 and 999
2) Search column three and delete any rows that don't match this number.
I have some code that would delete any rows within a certain column, based on predefined criteria and i've tried modifying it to suit my needs.
View 7 Replies
View Related
Aug 21, 2008
I am using the following piece of code to delete unwanted rows from a worksheet:
Sub DeleteDates()
FinalRow = Cells(65536, 3).End(xlUp).Row
For i = FinalRow To 1 Step -1
If Cells(i, 3).Value Like "*2007*"
Cells(i, 1).EntireRow.Delete
End If
Next i
End Sub
What I am working with is a sheet of about 5000 customer appointments going back to the beginning of 2006. A have peiced together a lot of code to format it exactly as I want and to create a pivot table of what is remaining after the rubbish has been deleted.
However I am finding that I need to keep amending and re-running this bit of code, depending on the date range I want to look at. For example the code above will delete all appointments made in 2007.
My question is: is there a way that I could incorporate an input box, whereby excel asks the user for a start date and an end date and deletes any rows outwith that range?
Some important info: All of the dates are in column C, in the format mmm yyyy
I would only need to narrow down to a month - so for example mar 2008 - jun 2008
View 9 Replies
View Related
Feb 19, 2010
I am looking for some help with my data, I have this excel sheet in which i have certain abbreviations and answers to some questions which we use on our daily job. Now what i want to do is create a search box on the sheet and find the matching Abbrevation on the complete excel. The trick here is to hide every thing else when i search for some thing and see only for what i want to see in order to not to get confused. I am attaching the excel to give an idea to what i am looking for.
View 2 Replies
View Related
May 25, 2013
I have a spreadsheet that I use throughout the day and have to send an email to another person when a customer makes a payment. The number of products they pay for will be different each time and I have the code to copy the right cells into an email and the code to insert the right number of rows for how many things the customer is paying for however as I use it multiple times through the day, I need the code to insert the right number of rows, copy it over to the email then automatically delete the rows it has just inserted so it is back to basics so I can use it again.
The code I have for the inserting the rows (from searching through forums) is:
Sub PRows()
Dim NextRow As Long
Dim NrOfCopies As Long
Dim i As Long
Const NrOfCopiesDefault = 1
Const NrOfCopiesMaximum = 9
[code]....
View 1 Replies
View Related
Jun 11, 2014
I am working on a project that I feel should be relatively simple but I seem to be stuck. My Goal: To add information to specific rows of data in the worksheet. I want to use a user form, and eliminate as much typing for the user as possible. I have already designed my user form, and written the code to identify the row in which I want to edit. Nonetheless, I do not know how to code the insertion of the new data. The data I want to add will be in empty cells at the end(right) of the data table.
I have attached a portion of my data set.InsertQuote.jpg
Here is where I am. The words in red are just colloquial words that I cannot seem to put into code.
Private Sub CmdInsert_Click()
Dim Company As String
Dim PartNumber As Variant
Dim Condition As String
[Code] .....
View 1 Replies
View Related
Aug 8, 2009
If the three letter code (in the second field) matches one in the range,
I'd like to input the company name into the cell beside it. I'm trying to import a list such as this:
3D OIL LIMIT,TDO,Energy
3D RESOURCES,DDD,Materi
3Q HOLDINGS ,TQH,Softwa
4C SECURITY ,FCS,Techno
A-CAP RESOUR,ACB,Materi
A.B.C. LEARN,ABS,Consum
A1 MINERALS ,AAM,Materi
AAQ HOLDINGS,AAQ,Food B
etc...........
View 3 Replies
View Related
Nov 7, 2012
I would like to delete a column using data validation list. for example :
i have a data validation INPUT list in cell A1 (Part No., Supplier 1, Supplier 2, Supplier 3) the heading start from B2,C2,D2,E2,F2. If user select Supplier 1 from data validation list in cell A1, the column Supplier 1 [c2] should be deleted.
And/or also if in cell B1 have a data validation INPUT row list (Part no.) & if user select part no.,
the selected part no. row should be deleted.
View 1 Replies
View Related
Apr 1, 2009
I have a large *.tab file with around 450,000 records. I opened the file in excel and then filtered two columns where there are values equal to 0.
I then highlight all the rows and try to delete them, but I receive the following error popup box:
"Microsoft Excel cannot create or use the data range reference because it is too complex."
How can I delete all these filtered = 0 rows without getting this error??
View 9 Replies
View Related
Dec 30, 2011
I want to delete some unwanted rows on closing my excel file. the blank rows are between set of datas. so it has to check all the blank rows, delete it until the last one. example
data
blank rows
data
blank rows
data
blank rows
data
View 4 Replies
View Related
Apr 10, 2014
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
View 1 Replies
View Related
Apr 25, 2014
I have the following macro that imports data from several input files and rearranges it in a master file.I want to change it so that I can use it in each of the input files. Therefore, it should look in the input file for Spreadsheet "XYZ" and rearrange it in Spreadsheet "Data".
[Code].....
View 9 Replies
View Related
Apr 16, 2014
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
View 5 Replies
View Related
Jun 15, 2007
The Code below searches for a file and displays how many files by that name it found.
Sub Test()
With Application.FileSearch
.NewSearch
.LookIn = "C:"
.SearchSubFolders = True
.Filename = "Temp.txt"
If .Execute > 0 Then
MsgBox ("Success: " & .FoundFiles.Count)
Else
MsgBox ("Fail")
End If
End With
End Sub
However, Flashdrives are a common use among those that will use the code, such that I need the code to not only search the hard drive C: but all hard drives and flash drives. Using .Lookin = My Computer did not find the files stored on my Flash Drive.
It finds it using .Lookin = "K:" but I can not rely on my flash drive always being K:. It does not find it using .Lookin = "PumaFlash" (Name of flash drive)
I'm looking for one of two things: Code that will Search all hard drives and flash drives (optical drives can be included) at once Code that will Find path for all hard drives and Flash drives and then search them separately
View 2 Replies
View Related
Nov 26, 2008
I have a number of project files that are maintained on the network.
These files are named "XXXXXX Project Name.XLS".
XXXXXX is the project number. This numbers are unique, so there is only one file per number within the directory. Project names are mostly unique, but sometimes they could be same for a specific site.
We recieve financial data in excel file format from home office. These reports reference only the project numbers, not the name.
I have created an excel program that extracts project numbers and related financial data from the report [from home office].
I would like to create a button or check box next to each project number in my program and link it to the respective file on our network. Once I access the local file on the network I could copy the financial data in its right place.
The problem is opening the file based on project number only, while the file names contain Project Name as well.
So, can I look up a file by only searching for first six characters of the name?
Or can the Hyperlink function work with some sort of wildcard? I can write the formulas to create the hyperlink and then write a macro to look for that file, but I need to be able to find and open the file by looking up only the first six characters.
View 9 Replies
View Related
Jul 14, 2009
I have been asked to fix a macro that is supposed to search column B and find any cells ending with .mov. Any row fitting the criteria is to be copied to Sheet2. The file is a internet traffic log and we are trying to determine the number of downloads we have on our video files, which are all .mov format. Sheet1 can exceed 3000 rows.
Here's what I have so far:
View 2 Replies
View Related
Dec 11, 2013
The sub i'm currently using does an instr search for a unique string in an xml file. This works fine but i'd like to speed up the search if possible because I have a large number of xml files to search. My problem is that i have very little experience in parsing xml.
I notice that if i load the xml file and look at the namespaces i see item(1) through about item(20) and the unique string is always contained in one of those items. Note that the number of items can be different for each xml file i load.
The xml file is loaded into an object i define as oInstance and i'd like to do something like:
Do
counter = counter+1
if oInstance.namespaces.item(counter) = uniquestring then[code]....
but now i get Err.Number 91
View 9 Replies
View Related
Sep 12, 2006
I wrote some code which has compiled a LOT of text files telling me what computers have licence for certain software.
The name of the txt file is the computer name and data within is simple:
--------------------
Audit of GQL license
Audit of Visio license
Audit of Frontpage license
Audit of Project license
Audit of Visual .NET license
Audit of Word (Office) licenses
Found Office
--------------------
as we can see here this computer has 1 licence for OFFICE.
If the string "FOUND" is found then i want to be able to paste the licence name in a spreadsheet and in the next cell underneath paste the filename it was found in the e.g.
VISIO
comp1,comp2,comp3,comp4
WORD
Comp2, comp3, comp4, comp5....and so on......
View 9 Replies
View Related
Jun 5, 2009
is it possible to type a document name in a cell,then run a code that searches a root dir and sub folders, find the existing document and open it?? for example
cell: "D20"
filename: DYAZES-001
root dir: "I:IsolationDataBaseIsolationProcedures
subfolders: DryMillA, DryMillB, Despatch, Maintenance
View 5 Replies
View Related
May 20, 2013
I have a macro that takes a text file as input and produces an excel file as output. I want a macro to store the name of the text file in a variable (without its file extension).
View 1 Replies
View Related
Jan 26, 2008
Below is the current code I have for File Copy before the workbook closes. This file will be distributed all over and obviously will not have the same old path and new path locations as I have in my code also will not have the same file name. Is there anyway to still perform the file copy without knowing the old path and file name and possibly have message box pop up to ask the copy to location and use that in the new path string?
Sub Macro1()
Dim fs As Object
Dim oldPath As String, newPath As String
oldPath = "I:EXLDATAMC Daily" '<---Where the file is currently located
newPath = "H:South RegionOrlando Mail Services2008DI" 'Since the super shared drive is super slow we will just copy and replace this file each time before we close and of course after we save
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile oldPath & "" & "OrlandoMail.xls", newPath & "" & "OrlandoMail.xls"
Set fs = Nothing
End Sub
View 4 Replies
View Related
Feb 22, 2008
I recorded a Macro to open a workbook, copy and paste data from one excel workbook to another, and close the workbook which was opened. Everything works fine except:
because of the amount of data that I am copying, I get a message box saying "There is a large amount of information on the Clipboard. Do you want to be able to paste this information into another prgoram later?" with a Yes/No button.
So now I have to click the "No" button before my macro closes the excel file. Is there a simple line of code that will by-pass this?
I am assuming it has to do with the following:
Windows("Book1.xls").Activate
ActiveWindow.Close , savechanges:=False
View 5 Replies
View Related
Oct 11, 2013
I am trying to write a macro to open a specific file, but need to search multiple folders within folders to find it.
The file name I need to open is "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx".
I need to drill down to the Adherence Report folder and then have the macro search through folders for each year (2012, 2013, 2014, 2015, etc) and then each month within each year (1 January, 2 February, 3 March, etc), at which point I would then find that day's file.
So far I have the following:
Code:
Sub Open_ESCL_Report()
Workbooks.Open Filename:=*****.****.****.******.comsharesPurchasingTeam XEscalationAdherence Report & "Escalation Adherence-Details " & Format(Date, "mm-dd-yy") & ".xlsx"
End Sub
Each file is stored in it's respective month folder as .....Adherence Report(Year)(Month)(File).xlsx
View 3 Replies
View Related
Apr 2, 2014
I have a lot of rows of data all divided with an empty row and if they are 6 or less all rows need to be entirely deleted any 7 or above need to be kept.
View 2 Replies
View Related
Jan 29, 2014
getting full file path from selected input file?
I have a macro with text box and browse button.
User can click on browse button and it will allow them to select only text files.
Now for further processing I need to get the file path and file name in 2 different variables.
Like if user selected C:/Temp/Test.txt
Variable 1 should have C:/Temp
Varialble 2 should have Test.txt
View 2 Replies
View Related
Nov 20, 2008
Let's say I have the following file which is imported via a macro which comes from a txt file. [Apologies it's 7mb zip file] http://www.fileden.com/files/2007/8/...mpNotFixed.zip
The way the txt file is that two rows make up one row, but SAP spits the data out into two rows. Now there are specific things that I need from these tabs. The way i can get this data is to perform sort by descending on the tabs. When you sort by a descending you get the data that i need [at the second half of the worksheet]:
Basically what I want to know when i create a macro, how can i get it do delete all the rows above a specific row. In the attached file, go to DataImport2.
I would like a macro to find the word "English" in column C and then delete all the row's above it. I cannot record this because it copy's the specific cells, which i do not need. DataImport3 is how the data comes out, All i do is sort by descending ... and i need help as stated above to delete all unnecessary data.
View 3 Replies
View Related
Feb 14, 2009
I simply want to delete the last 10 rows containing date from a worksheet. No criteria other than they contain data anywhere in the row.
View 4 Replies
View Related
Oct 26, 2009
I have a worksheet with that I need to delete both rows if the data in one of the columns is the same. I know how to delete the duplicate row, but I need both rows deleted..........
From the example above I would want Rows 1,2 and 10,11 and 12,13 deleted based on the data in column D
View 2 Replies
View Related