How do I write a macro that will delete every other line on a spreadsheet? I can create a macro that delete one line using the Record a macro command but what do I ad at the end of the macro to keep it going until stopped with Ctrl Break?
I have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.
I have about 180 workbooks which I need to compile into a Master Schedule.
All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?
Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?
I need to read or clear all the line in specific range which will be increasing dynamically. Problem is "Selected File List" table range is not specified. New file chosen with browse will be added to "Selected File List" dynamically. Number of file can't be predicted.
vbaStk.JPG
What I've tried so far is, keep track of the browse button click and add the file path to arraylist. After that, (Row number 9 which is start line + arraylist size) to get the number of line of end of the table. But due to some requirement, I want to read the file from excel file. Something like -Read until found blank line or border bottom or something.
I'm new to VBA and I'm not so sure what I'm doing with vba codes
trying to get macro to loop until there's no data to process.I have data in two columns A&B which it creates a chart from I then need it to skip C (which is blank) and do the same chart for columns D&E and so on indefinitely until there is no data to process..
I have attached a very simplified version of the data, the data is made up so the charts don't make sense but if you run the macro you'll see what I'm trying to achieve.
I need add a new worksheet that outputs sheet name and box 7 data for each sheet onto a master spreadsheet. I have been playing around some code but adding a third loop is a bit of nightmare and not ideal so I've just output to msgbox for testing per. Would application.match be better to use than the 'is like'?
I want to use a piece of VBA to copy values from cells A1, C1, E1, and G1 and paste them into the next blank cell in an existing range called "DCopy". I am trying to use the following code, but as I have never tried writing a looping macro, have no idea what I am doing:
Question: I have a VBA code that transpose every 7th row of a dataset in column A only, however I would like for the code to extend the transpose to (7 row and 3 cloumn).
I'm trying to create a macro to loop through daily one minute data.I believe the flowchart would be something like:
Create Variable
For each day in recordset
Loop through each minute record
Run system rules
Copy to Seperate worksheet
End
Additional Info:
Data is in columns B-G (Date,Time,Open,High,Low, Close)
Sample system could be something like:
If Current record close price is > Past 2 records Close Price Then Buy 100 Shares Liquidate if poistion moves against by 10% Take Profit if position increases by 5% Else close by days end
I'm using Excel 2003, version 11 and am trying to reformat data from one sheet to another. What I would like to do is copy the rows for each team from the Input sheet (Input tab) and and use them to build rows on the Output sheet (Output tab) for each possible combination of players.
In my attached example, a three person team has three combinations and a five player team has ten combinations and so on. I know this is probably a loop within a loop but I can't seem to get the right combination. Not all teams are full yet so the number of players on a team will vary. I need macro code to make the input sheet look the output sheet.
My spreadsheet's column Z is a delivery date. When the workbook is started up (preferrably once per day, rather than each and every time you start it in a day), I would like to be able to loop through column Z, and if the date is today, toss the same row's columns A, D, and J on to `Summary`!A2:A?? (however many rows it needs), concatenated. I know I can do it easily with a for loop, but as I've learned, for loops tend to be really slow. Is there a quick or better way to do this than with a for loop?
Some code below which I have inherited, basically its looping through one column of data and removing the text at the beginning before the hyphen eg. '123 - data text' to 'data text'.
HTML Code: 'Remove everything before the hyphen in the activity column Dim SearchStr Dim CharOffset As Variant Range("c7").Select
I am trying to create a line graph that will incorporate multiple columns of data in one series of data. The reason I do not place all of the data in one column is because it could exceed the maximum amount of rows allowed in excel. Also I need the data split up for viewing purposes.
I can easily just graph one column but how do I combine all the columns into one line graph with the data being in separate columns. Basically all the columns will be my Y values and X values are just 1:n.
Example Below:
Column AColumn B Column C159261037114812
Now in the example all of the values are x values.
I want to pull data from another sheet, however there are multiple listings of each and I want to pull the data to another sheet for each line. I maybe easier if I try and show below:
Col. ACol. BCol. C Smith101 Jones512 Green65 Black1214 Smith 3612 Jones1512 Dual25 Green1225
I want to pull this data for each name in Col. A in to another sheet. I've tried "IF", "Vlookup" and a couple of others, it just seems to be hitting the first option and pulling the data but not the ones below.
So if I selected Green on the second tab it brings through the "6" and "5", but not informtion from the listing from Green below. So I wiould like to list all the Green's, and the applicable data.
Our small family business has a mailing campaign that we track with excel. However, we're very concerned about users opening the macro-filled master spreadsheet. Instead, we'd like a macro to do everything for them.
When a letter comes back in the mail with a bad address, the user types the Street number and street name such as "1234 Main St" into $A2 of c:dropbox eturned.xlsm, presses the macro button, and it should do the following:
Opens and Searches "Sheet1", "column S", in the file c:dropboxmaster1.xlsm, and finds the LAST instance of the address typed.Selects that entire rowCopies the entire row.Pastes the contents into the row of the active cell in the destination spreadsheet, overwriting what was there before. Such as $2:$2 if the address was typed in $A2.In the master1.xlsm spreadsheet, sets the entire copied row color to "gray".closes master1.xlsm and saves changes.
The end result is that the user now has an identical row of information in their spreadsheet, and the master spreadsheet's row is colored gray indicating it has been completed.
Other notes:I'm open to more efficient steps than this if you have them.There are approx 5,000 records to search through in master1.xlsx at any given time.
I'm trying to combine 2 cells that both have text on multiple lines separated by line breaks. I want to keep the formatting so that when both cells are combined, line one from cell 1 and line 1 from cell 2 are on the same line in the new combined cell. This is easy to do when there is only 1 line in each cell but how do you do this for cells with multiple lines? Here is an example of what I am trying to do:
A B C
1 First Name: Last Name: Address: John Smith 123 Harold Street First Name: John Last Name: Smith Address: 123 Harold Street
Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1
I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,
I need the Service Order tab to populate with data from the CPR tab based on the IO#. Since there is more than 1 occurrence of an IO# I need a formula to list the 1st, 2nd, 3rd, etc result of the vlookup in the cell under the 1st.
=VLOOKUP($E$2,CPR!$L$4:$AA$12,2,FALSE),
When I drag this down I get the 1st occurrence of the IO# 777 I need all of them.
B C D Atex ID#(unique id) Campaign name Site Row 7 1st occurrence of IO# 777 Row 8 2nd occurrence of IO# 777 Row 9 3rd occurrence of IO# 777
I have a worksheet that I need to be able to easily copy and paste the formula/formatting from the row above, but not the Values entered, (if any). I am using columns A to DG.
I just really need to be able to copy all the formula in these cells down - nothing exciting or fancy.
I've tried formatting as a table as per some searches and that was difficult! My column headings changed, and when I inserted a row, two of the formula's wouldn't copy down - plus the shading and borders went wonky in places.
Currently I am using Excel 2000. I did a search and couldn't seem to find the answer I was looking for. Hopefully someone can help. It would be greatly appreciated since I have been trying to figure out how to do this for days.
I am trying to create a worksheet that will pull info from a row on one sheet to another sheet based on the name; then continue to list the info on each line afterward until there is no more of the that particular criteria. See below (this is just an example to simplify the data but would need the same process):
This would be the data on sheet 1:
Name Bonus Commision
Tom $45 $50 Mary $25 $75 Mary $30 $80 Tom $60 $50 Tom $90 $25
What I would like to do is have 2 more sheets. One would be Tom's sheet and the other would be Mary's sheet and it would look like this:
Tom's Sheet:
Tom $45 $50 Tom $60 $50 Tom $90 $25
Mary's Sheet:
Mary $25 $75 Mary $30 $80
I am not completely familiar with all of the functions in Excel and compared to you guys I am a complete novice.
Is this possible to do in Excel without using a macro?
I have a large amount of data pertaining to many different runs in which I am given the mass to charge ratio of a compound. My goal is to identify these compounds, and to ease this process I am trying to find a formula that would “line up” near identical masses while providing a blank if no mass was found in that run. For example, if I have:
It would be important to have some sort of tolerance setting, as the readings are not always exactly the same. Also, I’d normally have about six runs of data to line up.
I have a spread sheet with product codes on and 5 different prices types but they have only pulled through 4 times so need to insert a 5th line for each product code
I have a spreadsheet where on a weekly basis data is copied in to various tabs. I then have a "formula" tab where I have a single line of formulas which look up the various data tabs and extract the results I want to show.
Currently each week, before I import the new data into the various tabs, I copy the last row in the "formula" tab and paste to the line below it. This contains all the working formulas. I then paste values only on the line that I copied, thus "locking in" the values it calculated with that weeks data. This means that each row then contains the results with that week's data, and this will grow week by week.
What I am looking for is a formula that automates this process. So let's say that row 30 is the final row of data on my formula tab, it contains the formulas I want to use. I would need a macros that does the following:
1. Looks up last row (row 30) 2. Copies last row (row 30) 3. Pastes to next empty row (row 31) 4. Pastes values only to second last row (row 30)
I'm not too great with writing macros, I've found plenty that can find the last row, but I can't get them to work to highlight that row.
