I am looking for a macro to do the following:
Once data is copied to a tab labeled "data", it will sort on column H and delete all rows of data that do not have a AI or an RI code on it. Is this difficult to do?
I have attached a worksheet to illustrate a "Before" (raw data) and "After" (desired results).
Each row contains data on a forex trade. The variables which must be assessed in each row are:
Currency Pair (e.g. GBP/USD).
Opening Time
Closing Time
What I need to do is delete many rows in the raw data because they reflect a condition I do not want in my analysis, which is having multiple open trades of a single currency pair during the same time period.
To perform my analysis I need to delete all trades (rows) which meet the above condition.
The worksheet provides examples, along with a rudimentary tool I've used to help me sort/delete rows manually.
Since I am looking at thousands of trades, a manual sort/delete is impractical. (my eyes don't like this kind of work!)
The rudimentary tool I've used in manual sorts/deletes is simply to have a column which returns a "1" if a trade opens before an older trade has closed. I do this by first sorting the data by (a) currency pair then (b) date opened. Then I must manually go through multiple iterations of deleting trades until there all of the "1's" have disappeared.
I'm hoping someone can show me a macro which might do this sorting/testing/deleting automatically, at least to the point where all I have to do is repeatedly press a "macro" button until there are no "1's"
I need to sort the following data by the CODE number shown while keeping the Description and Quantity attached to the various codes. (Excel is smart enough to first sort the codes numerically then alphabetically, which is what I need) The client info (bottom) also needs to be left alone. Done correctly, after running the macro, the order would be 04cls, 04fba, 08jud, with the Desc. and Quantities moved with the codes. At the moment each line is stored in a single excel cell.
Description = Item1
Code = 08jud
Quantity = 1
Description = Item2
Code = 04fba
Quantity = 1
Description = Item3.........................
how to sort a selected range.
The end of the range could be any cell. The top of the range is fixed in A1.
I've tried with this:
Sub Order()
Dim final As Variant
final = Selection.End(xlUp).Address
Range("A2").Select
Range("A1:" & final).Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range _
("G2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
End Sub
i want to create a command button that would sort my data in descending order.
View 7 Replies View RelatedI got a complicated macro that I need some help on. I want to write a macro that does the following (for clarity, I have attached an excel file).
I want to write a macro that automatically copies the rows in worksheet 'boekhouding' to their corresponding worksheet. The name of the worksheet, to which the data of each row has to be copied, is specified in row I (Beware: if it is not in row I, it is in row H). The names of all destination worksheets are given in the blue cells in worksheet ‘rekeningen’.
For example, row 2 (of worksheet 'boekhouding') should be copied to worksheet 'ABD'. Row 3 (of worksheet 'boekhouding') sheet be copied to sheet 'bestuur'. Row 4 (of worksheet 'boekhouding') should be copied to sheet 'Commissiekosten gala'. Etc for every datapoint (row) in worksheet 'boekhouding'. The number of datapoints will exceed 300, so manually doing this will be too time-consuming and unreliable.
please have a look at the attached file.
I want to sort the data by the comments in Column B, specifically by comments that do contain a telephone number, whether it is "Telefon" ( e.g. in B17 ) or "Phone" ( e.g. in B24 ) or "Téléphone" ( e.g. in B16 )
I know a Macro written in VB could do that.
I am looking for a macro that will allow me to the following:
- search column F for "word1", "word2", "word3", and/or "word4"
- then match the search to row on column C data
- then copy/paste row or rows of matched data unto "Sort" worksheet but only data from columns A, B, C, and F
- also, when copying, copy the row above
I have a worksheet(1) that contains data like: date, name, price, size....etc. I need to "sort" by size (only use 2 values - large and small), then copy each to another worksheet that saves data by the Month. The amount of data on worksheet(1) changes monthly.
View 9 Replies View RelatedI have a worksheet that is automatically updated based on actions in other sheets. There are 10 columns of data (A-J) Headings are on row 6 and data starts on row 8. I have created 3 buttons to sort worksheet by client column (B), year-end column (E) and to bring it to original order (by numbers in column A). I have created the following codes that I have assigned to each button however it does not work.
Sub Macroclient()
Columns("A:J").Sort Key1:=Range("B8"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
[Code]....
a way, without using a macro, to have a specific column automatically sort data being pulled in from another column, so that even if the data from the initial column gets changed, that the destination column will take that change into effect and update the sort automatically?
View 9 Replies View RelatedI have a report that I drop down into Excel that is a transaction report for securities. The data for each security is in two rows. I would like to write a macro that would sort each two-line group by a certain cell in the group. If I could make the macro request the number of rows and columns in each range and the cell address of the cell to sort by, I would be able to use this macro for many different reports. Also, there has to be some way of telling the macro where to begin and where to end. I have some experience with macros although generally I "Frankenstein".
I have a concatenated value in a column D (from three colums A, B and C). Whenever a user enters data in colums A, B and C, I would like to get a sorted list of values from column D to be populated in column E. I use the sorted values in Column E for a dropdown list in another worksheet. Could somebody help me with the macro code?
View 9 Replies View RelatedI have sheet 2 with a table with data in A2 thru X500, A1 thru X1 are my headers, A1 data is either Forecast or Sold, B1 data is Month, C1 is Value. I first need to Filter the data by Forecast, then by a specific month then by the value in decending order (keeping in line the data thru x which are headers I don't need to sort by). Next I need to take the 1st 10 lines by a specific month; A thru X and copy it onto a defined section on Sheet 1.
I then need to do this for the 2 more times for the 2 proceeding months. So If we start with Jan, I need the macro to do Feb and march as well.
The end result is that I have Sheet 1 with 3 sections on it each section is 10 lines and the resulting data is my top 10 forecasted deals for Jan, Feb, March. The macro must be able to do this for each month, so if June is chosen the 3 months are june, july and Aug.
I am new to VB Macro creation and I am creating VB Macro which will:
1. Sort data in Columns within an active spreadsheet
2. Create new Worksheets
3. Delete Values in rows based on value in Column.
I have listed my "Step by Step" instructions in the tblTest Excel file on attachment. The instructions are clear and straight to the point.
Below are some VB Macros I was experimenting with but it is not complete.
Sub Sort_Ascending_With_Header()
'Sorts a worksheet in ascending order and assumes there are headers on the data
Range("A1:DZ20000").Sort _
[Code].....
I am putting together multiple worksheets with dumped data that should sort themselves by the press of a button. Each entry has a 'code' and a value and they are sorted by the 'code'.
At the moment i have the first worksheet sorting correctly and i am trying to program the second worksheet to sort data into the existing worksheets if they exist or create a new worksheet if the data doesn't have it's own worksheet.
Here is the code i am working with.
The first sheet is the row data given to me, the second one is where I want my data to be analysed automatically by macros.
I managed to create a pivot table manually to give me the info I want but then I still have to copy all the results from the pivot table sheet to my analysis sheet.
How can I create a macro able to do all that by it self? I tried to record a macro using the recorder but then when I try to play it it gives me an error straight at the beginning in the definition of the pivot table I think.
An example of what I want the pivot table to do is:
in the first sheet, go in the small table in column K and L, take the value of L1, then in the row data in column A to J, in column C look for the value in L1, once found, look for the value 1 in column J then do the average on the values in column E and put the result in my second sheet in cell F5
then do the same but look for 2 in column J and put it in F8, then 3 in F11, ... until 7 found (the data is in ss.000 and so the results in the second sheet should be formated the same way)
then do the same all over again for value in L2
etc etc etc
this should be done for the 6 values in column L, each having values up to 7 in the J column.
Then I need to do the same for other columns and not only averages but minimums as well but I can adapt the code I think.
I have an issue with an export file which is produced in CSV format and needs to be aligned. I have attached a sample of what I refer to.
My Source excel file looks like:
ABC A-101 B - 202
DEF B-203 C - 100
The destination should align with the relevant column headers and place a blank if it doesn't match. Other have queried a similar thing, however my header and data and description is actually in the same cell with a dash separating them.
The output I want is:
A B C D
ABC 101 202
DEF 203 100
There are multiple row of employees with varied column headings as its dependent on what system access the user has. The headings i.e. A,B,C for example is a fixed number of headers.
In my real data set the headers represents a 3 letter system prefix e.g. ZCR,ILP etc
I havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.
Sub SortMeetings()
Dim iCTR As Integer
Dim yCTR As Integer
Dim zCTR As Integer
zCTR = 11
For iCTR = 12 To 23
For yCTR = 1 To 10
If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then
Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value
zCTR = zCTR + 1
End If
Next yCTR
Next iCTR
Range("AA11:AA" & zCTR).Select
Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
I have a worksheet that contains multiple task lists, each having two columns, a "Priority" field and a "Description" field. The data should be sorted by Priority first and by Description second. The header row is 5 and the data is in rows 6 through 50. The first list is in A5:B50, the second C5:D50, and so on until the sixth list in K5:L50. I have a macro that works for one task list, but cannot get it to function for multiple task lists. Below is the macro that functions for the first task list. It is in the code for the sheet tab.
Option Explicit
Private Sub Worksheet_Change(ByVal Target1 As Range)
If Target1.Column = 1 Then
[Code]....
I am looking for a macro to sort my colums in ascending order the range i need sorted is from A7:F107, it will be sorted with the numbers in the A column ie 1 - 107. sometimes different numbers get added and it has to be sorted in number sequence.
If possible i would also like a macro to protect this range after the sort,
I am working with Excel 2003. I have a series of spreadsheets generated by my companies database program. For a special project I am trying to work on, I would be able to get my results much much quicker if I could get a macro that would do the following:
The first row is a list of headers. I need the macro to look at each cell below the header, and if any data exists in that column, I need it to delete the entire column. The results I will get will tell me quickly which headers are in certain jobs but are constantly not being used. They are trimming the database at work and this type of macro would help me tremendously.
I have a large dataset with 21 columns and 3000 rows, but to keep it simple, the data looks more or less like the first dataset in the attached excel file.
This results in 3 curves (time vs force). However, there is excess data in there, I only need data as soon as the Force becomes positive (>0) untill the Force drops below 0.02 again (I consider that the end-point of my curve). So, this dataset would look like the second set in the excel file after the macro-manipulation.
I think one of the difficulties is making sure that the macro doesn't cut of my data right in the beginning, when the curve first crosses 0.02. In that case, you get the last dataset. And this is not what I want...
I hope I explained my problem well, if not, please let me know.
So, is there anyone who knows how to do this?
By the way, is there anyway to insert small excel tables in this post? Cause I wasn't planning on having an attachment, but if I copy-paste the data in this post, than the formatting gets undone and it looks like there's no way to fix it...
In the sample below I need a macro that will delete the entire rows
where the data is repeated in a given column---that is, that it would delete
the repeat ones, leaving one of them. In this example, in the first set, all
that should be left are two rows which contain ME22N and MR8M in column E.
ME22N is repeated two more times and those rows need to be deleted.
If I could get a macro that would ask for the column to operate in, that would be
great for other spreadsheets where it would not necessarily be column E.
ABC DE
MACLEOLLynn MacLeodP0040104ME22N
MACLEOLLynn MacLeodP0040104ME22N
MACLEOLLynn MacLeodP0040104ME22N
MACLEOLLynn MacLeodP0040104MR8M
MACLEOLLynn MacLeodP0050006MB0A
MACLEOLLynn MacLeodP0050006MB0A
MACLEOLLynn MacLeodP0050006MB0A
MACLEOLLynn MacLeodP0050006ME21N
MACLEOLLynn MacLeodP0050006ME21N
MACLEOLLynn MacLeodP0050006ME21N.............
I need a macro to delete rows that contain specific data in column A. For example, in the attached spreadsheet below,I need the macro to look for data in rows A1 - A7 (highlighted in yellow) and then delete the rows. The same data occurrs several times in the spreadsheet. The macro will need to delete all rows where this data occurs in column A.
Microsoft Excel - Book2___Running: xl2002 XP :
OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutD9=
ABCDEFGHI1
[Code] .......
I have an Excel Workbook with a Paid sheet and an Outstanding sheet both sheets have a File Number column.
I would like to create a macro that looks through the Outstanding sheet and if the File Number from the Outstanding sheet is also in the Paid sheet can the entire row be deleted from the Outstanding sheet.
i am in need of a macro to delete the rows with data only which then leave the sheets cleared and ready to use the next day. if i create a macro to clear say fifty rows of data and then tomorrow there could be 75 rows of data and the macro will fail clearing the sheet. if there is a solution can i just say i can create macros but never stepped into them in visual basic to change them, i guess your thinking what do i do if the macro doesn't work. and the answer is indeed yes i delete it and start it from the begining again and again till i get it right.
after importing data (data >> get external data >> import text file) and running a macro that contains
Cells(i, 5).EntireRow.Delete Shift:=xlShiftUp
i cannot insert any data into the next column below. why is this so? is it because of the excel memory that remembers that a macro has been previously ran in that cell before? example i import text file into B1 then the data will be imported into different cells using delimiters. the data will occupy from row 1 to 22. After i run the macro which combines fields with similar data the data will occupy row 1 to 15. then when i try to import data into B16. the "import text file" option will be grayed out. why is this so? how do i solve the problem?
I receive spreadsheets each month in the same format. It is effectively 6 rows of titles, 7 rows of weekly data, 2 rows of weekly summary, 3 blank rows then it repeats itself with the 6 rows of titles etc... down the page for 8 or so weeks.
I wish to create a (looped) macro that will leave the top set of titles and the 7 rows of weekly data (Mon to Sun) then delete the next 11 rows and so on and so forth to the end. I will then be left with the intro to the dataset followed by a whole months worth of data in continuous format as opposed to weekly summarised sections. But I am stuck as my macro knowledge is basic and I can only record macro's not program VBA.
I can create a macro and manually delete all entries for one spreadsheet but would rather have a code to do this than can run (x) amount of times in case the size of data changes. When it deletes section by section, the cells move up and the row numbers change.
I am trying to write a macro to delete all connections from a very large excel file
I created this file by using a Macro to extract data from a external data source (url's listed on a sheet)
The issue is the 1000+ connections on this file make it next to impossible to operate
I cannot recreate the file
macro to delete all connections in all sheets in a given work book.
[URL]
Macro used :
Sub Dump()
Dim myURL
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Select
myURL = Worksheets("Sheet1").Range(ActiveCell.Address).Text
Sheets("Sheet2").Select
Range("A65536").Select
[code].....