Been getting into Excel recently for my job and have been learning things at a quickened pace, which is nice. However, I have ran into the same problem everyone else has, at one point in their Excel career, run into. I need to sort merged cells.
I am sure you are thinking "Unsort" or "do you really need to merge them?" Well when dealing with nearly 300 names, the amount of sorting become monotonous. I am sure this will not be the first time (it's actually the second) I will need a macro like this.
The current set up is:
4 merged > 2 merged twice > 4 single
If you need additional information on the format let me know, but I am looking specifically at the 4 merged into what I'll be sorting.
i have a problem to sort merged cells in the file attached. i tried via macro but could not reached a good solution. I have 30 plans inside the sheet and even manually is too hard to do it because i use some "=" to not type again every number since some are the same but when i try to sort the lines became diferent values.
I have a fairly simple sheet that I'm trying to sort (and have in the past) using Data/Sort on 3 columns.
I do not know what a 'merged' cell is, so have no knowlingly formatted a cell as 'merged'. I have populated some cells with information copied from other applications, so they may be 'merged'.
When I try to sort I get the message: - this operation requires the merged cells to be identically sized. to avoid this behavior, unmerge all the merged cella in the range or....
How the heck do I even find the merged cells that are causing the problem?
I have a list of text values in column X. I need to come up with a formula in column Y.
X Y (RESULTS OF REQUIRED FORMULA) Comment 1 HAT 1 First HAT in column 2
[Code]....
I can't play about with the natural order of the spreadsheet, so there's no chance I can re-sort the data into column X and (easily) identify the duplicates that way. So, it could be that the duplicated value(s) will appear in any cell within that column.
I need to identify whether the item is a duplicate in the unsorted list. Ideally, the first entry of a set of duplicates will be given 1, then the subsequent duplicates themselves given a 0 (zero). It's to subsequently do some counts on.
I guess that as long as the one of the entries in the duplicates is marked with a 1, while the others are 0 (zero), that's all that's important.
I have several columns "A" thru "J" with simple contents, part#, qty, date, etc. and in column "K" descriptive text which can run several sentences long.
I'm trying to record a macro to insert a row, merge "A" thru "J" in that new row, cut the text from the "K" cell in the row above and select wrap text so it shows up below those headings so my user can read it.
Would look like:
I can get it to look like that if I grab the row borders and drag it to fit, but can I get it to increase row height and wrap to fit the text length automatically with the macro?
Code looks like this so far:
Keyboard Shortcut: Ctrl+Shift+M
Dialog box pops up "This operation will cause some merged cells to unmerge.
I'm going to have a shortcut key in the macro to do this one row at a time, as the data isn't that extensive, but I don't want to have to drag all the row heights.
Column A's rows are merged according to column B. E.g.
Column A:
Row 1-7 (merged) Ron
Column B:
Row 1: New Jersey Row 2: New Delhi Row 3: New Jersey Row 4: New York Row 5: New York Row 6: New Jersey Row 7: New York
My result should be like:
Column A:
Row 1: Ron
Column B:
Row 1: New York
and similarly for other rows also.
PS: The no. of rows merged for column A can be different.
Also the keyword that should be present in Column B should be according to hierarchy, New York, New Delhi and then New Jersey i.e. first the macro should check for New York, if its present (in 1st 7 rows according to example given) then New York should be written in Coulmn B, if New York is not present then it should check for New Delhi and New Delhi should be written in Column B and if New York and New Delhi both are not present then it should check for New Jersey.
There are only 3 keywords which need to be checked i.e. New York, New Delhi and New Jersey.
If these 3 keywords are not present then the macro should not do anything.
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,
A1 - Header - "Holidays in XXXX' B1 - Header - "Holidays in YYYY' C1 - Header - "Leaves by YOU"
A2:B11 have static dates consisting of 10 dates in each column. C2:C11 - the user may enter any date at any point of time.
I would like to auto-merge the dates in all the 3 columns (A2:C11) in a single column say D2:D31 and then the system should auto-sort the column based on dates in any one order. So as soon as the user enters a value in say cell C2, all the 10+10+1 dates should get sorted.
Also the constraint here is a user may not apply all 10 leaves at in a year. So many of the cells may have blank values.
I have a macro to copy a merged cell from one sheet and paste it to another as a merged cell. Basically, I want to copy it AS IT IS and paste it AS IT IS. My code is below.
I have code that inserts columns, inserts formulas, and then copies the formulas to the last row of data. It all works good but for some reason, the columns that are being copied, are being copied past the last row of data. It can always be determined how far down it will be copied. Examples: if the last row of data was row 4, then the formulas would be copied down to row 24, if the last row of data was row 54, then the formulas would be copied down to 254, if the last row of data was row 284, then the formulas would be copied down to 2284. I can not figure out how and wh this is happening, but whatever the last row of actual data is on the spreadsheet, there is a 2 being placed in front of the last row of data and the formulas are being copied down to whatever the last row is with the addition of a 2 in front.
Here is the code for the columns additions, and formulas:
If the last row of data was row 54, then columns O:S would have the formulas copied down to row 254.
How can this code be modified so the formulas will not be copied past the last row of data?
How do you get the code boxes to appear in these threads. I do not know how to do this. As you can see, all I did was copy and paste my code in this thread.
I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields: Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.
1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).
2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.
3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.
4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment
5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
With guidance from this board in the past, I was directed to a macro that sorted information on a sheet (using values in column 1), opening a new sheet named by the sort title then copy/pasting all the relevant entries
The only restriction for this macro is the fact it is set to only sort information on sheet named Sheet1
I wanted to change this to an InputBox so the user can tell the macro which sheet to sort
I've got up to 150000 rows in excel (2007) and i need to sort the data into 2 sheets. Sheet1 contains all the data. Sample:
1000999999027FRESH PASTA FETTUCINE WITH TOM102002P N0809100000035000000012810000000+0000000+ 0000000000000000+0203001896409 2000999999027020300189640900000000000005740200070063
As you can see, the first row starts with 1000 and second with 2000. That would be the criteria. All other rows goes exactly the same.
I need macro that copies all the rows starting with 1000 onto sheet2 and all the rows starting with 2000 onto sheet3.
Filtering and copy/paste won't help, because the list is toooooo big. Thatswhy only hope is to find macro that loops threw the list.
I am now trying to write a macro which is a little over my head and was hoping some one may have some idea how to do it or have some code that is similiar and I can try to customise it myself. So here goes.
Basically all that happens is I paste data into a work book that always has two columns. The first column is a list of names and the second column is a list of numbers. I need to sort that information automaitcally every time it is pasted into the work sheet as follows.
1) Once any information is pasted to sheet 1 run macro automatically with no user interaction
2) All numbers must be rounded up or down first before sorting. If it is say 2.4 then it would become 2 and it it was 2.6 it would become 3.
3) Then move the numbers only from a specified column from sheet1 to sheet 2 and split them up into seperate columns based on the following results.
a) 0 - 3 gets moved to column B b) 4 -7 get moved to column C c) 8 - 10 gets moved to column d
4) Delete both columns from sheet1 after this is run so the user can re paste new information
5) If a user does this again then do the same but move into a new set of columns e f g and so on until the work book ends.
I have a sort function in one of my macro. Sometimes it works, sometimes it does not. I can't figure out why. I am trying to sort columns A-F and each column has a header.
I have a data set that id like to sort via macro. The first two fields are not relevant to the sorting proces.(Column A & B ). Column C contains totals some are zero, some are greater than zero and some are less than.zero. I have a macro which I use for many reports that sorts based on Autofilter but I dont know how to change it so that I can use less than, greater than and = to 0. Below is my attempt which didnt work.
I am trying to write a macro to sort a range of data by another column. Here's the code that I already have :-
Code: Public Sub sort_data(sort_range As Range, key_range As Range) ' MsgBox sort_range.Address ' MsgBox key_range.Address sort_range.Sort Key1:=key_range, Order1:=xlAscending, Header:=xlYes End Sub
However I get a run time error 1004 stating that the sort reference is not valid.
I am trying to write a Macro to basically do a lot of redundant formatting for me and I am stuck on one part of this code. Everything else works just dandy, but I am having the darndest time getting this Macro to sort by 2 columns instead of just 1.
I did the basic first step and recorded myself sorting the datasheet by both columns in 1 sort and got the code, but the problem is that the number of rows will change day-to-day, week-to-week, and so on, so I need it to not specify a cell number as the end of the range.
While searching around, I saw a very handy little piece of VBA code that will search for a header name, which is really nice because that would make it much easier to share this Macro with my coworkers who have different reports that all have columns in different locations.
So, I have a multi-part question. I will start with the most important one:
How do I alter this to not specify A9590 and D9590 as the last cells in those ranges?
Is there a way to have the Macro find where the header row is? The header rows in our reports can appear anywhere from Row 1 to Row 8 depending on how the report is set up (usually we have some information about the data, date range, etc at the top).
Dim c As Integer With ActiveWorkbook.Worksheets("Date XREF").Range("A5").CurrentRegion c = .Find(What:="Assignment ID", After:=.Cells(1, 1), LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Column .Sort Key1:=.Cells(1, c), Order1:=xlDescending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End With
And I want to sort it to look like the following, in order to track each number to a date and to enter into an external database...
Date #
1/1/11 1000
1/1/11 1001
1/1/11 1002
I need to list the entire thing instead of having starting and ending part #s. Any macro to do this? The actual spreadsheet has 250 dates and over 30k part #'s so doing it out is not really an option.
I am look for a macro that can look through a range of cells within a sheet and sorts that range of cells by word count.
I have 1 macro that is excellent which sorts by character count.
If possible, if this macro can be duplicated exactly the same, but to sort by Word count and not character count.
As a note the range of cells are always in a sheet labelled " AllKWs" Always start from Cell A3 running down. And the phrases could be anything like "car to rent in london"
As to the macro I have already for character count, it is this kindly written by VOGII
Sub NoOfCharacters() Dim lastrow As Long, TotP, TotC, AvC lastrow = Cells(Rows.Count, 1).End(xlUp).Row If lastrow < 4 Then MsgBox "Nothing to sort!" Exit Sub End If
I'm tryin to set up a macro to filter and sort and when I record the macro correctly it does not run properly.
I have detailed info below:
I created a sheet "Met Logbook" which contains all links to the source "Master Logbook". This master logbook has a lot of info added daily and we add info at the top of the sheet (we insert new rows every time at the top for different reasons). So what happens is the master logbook always changes its cells (when inserting new rows at the top, all the cells change row number). So I have just linked all the cells and more that we may be using in the Met logbook. This is why I need to filter and then sort the Met logbook (since there will be so many blanks, unuseful info and in the wrong order). (It will be in the wrong order because the "master logbook" has newer info at the top and I have more than one sheet of info for the master logbook) (so my links in the met logbook are somewhat out of order).
So what I need is to have code to filter column B with: ć and column H with "Blanks", then I need to sort column A (rec'd) in descending order (so that newer info will be at the top). I will post my Met Logbook sheet on my second post since its a little large.
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.
I have a spreadsheet that I would like to create a sort macro for. The number of rows will be variable but will always start in the same row. (i.e. ... it might be from A2:E200, A2:E300, A2:244. etc.) Some of the data fields will be blank.
How can I assure I am always getting the correct data selected to sort. I generally create my macros by recording and then sometimes tweaking them manually when necessary but I really don't know VB code very well. When I record a macro using the end/down and end/right combination, I get this Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select
This may or may not always work depending upon how many blanks I have and where they are located. Here is a simple example of what my sheet may look like. Note column F is blank and column G has formulas that looks at the cells A:E in that particular row for evaluation. The sort only needs to go to row E although if it went further, I guess it wouldn't matter. I'm sure this is an easy problem to resolve but I just don't know what code to use.
******** ******************** ************************************************************************>Microsoft Excel - sort example.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEFG1FIELD 1FIELD 2FIELD 3FIELD 4FIELD 5 FIELD 62AAA FORMULA3SSS11121010 FORMULA4DDD FORMULA5FFF 12 FORMULA6GGG26 9 FORMULA7HHH 7 FORMULA8JJJ 8 FORMULA9KKK14 8 FORMULA10LLL 16 FORMULASheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have 8 columns of data with each column containing approx 130 rows of information.
In the 8th column there is an IF function in every row that checks criteria and gives a result 'OK' or 'NOT OK'
Now what i would like to do is create a macro that sorts the whole table in terms of OK/NOT OK, so that the OK results are at the top of the table. Then after being sorted it copies all the OK data to another table.
This all sounds simple however, sometimes there might be 10 rows of OK data and other times there might be 50, and i need excel to identify all the rows of OK data to be copied to the other table.
So the problem i have is trying to tell excel to look for all the OK data and not just the first few lines.
This is pretty hard to explain so if you need further clarification please let me know.
I have a Ranking tab that has the persons name, weeks, months that subtotal into 2 columns, MTD and YTD. I have set up with 2 Macros to sort based on two columns based on their selection of MTD or YTD.
What I did not take into consideration is if a person was added at the bottom of the list.
Is there a way to modify this to include rows that may be added?
I basically need a macro to sort the sheet by V, U, T starting from row 3 and ending before the last vechicle. This is the problem though, the last vehicle ends everytime right before Car # in column A. So in other words in this example i would need it sorted by v u t, starting from 3 down to row 12. It ends at 12 because the next line includes car and a number.
Another sheet might have a longer list of vehicles and might end at row 100 before the next line has car number .
It always starts at 3, but the ending is dynamic where it should stop its selection before "car number ".
I would also like it to work on the current sheet im on, because i have up to 100 sheets like this and would not want it name dependant. Just want to click on a tab, and run a macro and have it sort from 3 down to the line before car in column A.............
I have a report I produce where I take an XML file, sort it and run some simple formulas then sort another way and run a few more formulas then update a pivot table. The XML files I'm using have the same headers but vary in length. When I set the macro up for a particular XML file everything works fine. When I import another one (different size) I get the error message:
Run-time error 1004: Sort method of Range class failed
When I look at the code I see that the range is fixed to the XML file I used when setting the macro up. Is there a way to auto-select the table in the macro so I can run the same macro on any size XML?
I have following code, which sorts data if user "double clicks" on cells A1, B1, C1, or D1. If the user "double clicks" on cell D1, I want to sort by column D but I want to apply a custom list. The data is in the "Custom Lists" table but I can't figure out how to apply to my code.
The sequence/sort order of the list is as follows: aaa+, aaa, aaa-, aa+, aa, aa-, a+, a, a-, bbb+, bbb, bbb-, bb+, bb, bb-, b+, b, b-, ccc+, ccc, ccc-, cc+, cc, cc-, c+, c, c-, ddd+, ddd, ddd-, dd+, dd, dd-, d+, d, d- code is as follows:
