Excel 2010 :: Sort Growing Column Starting Under Header Row At Cell C5
May 7, 2014
find macros to sort fixed ranges but not an entire column with existing header rows from 1 to 4.
Added to that is the need to keep the code compatible with Excel 2003 even though I have Excel 2010 at office (it's a file that needs to be "openable" in both versions so the vb code needs to be in XL 03.
Column currently starts at C5 but goes down to C47, at this point. But it's a growing list. Some of the rows are blank at the bottom, too.
I am looking for some to create a simple macro to sort multiple rows in ascending order based on the values in a particular column in the selected range. I want to sort A4:AI1004 in ascending order by column D. I recorded the following macro in Excel 2010:
Sub sort() ' ' sort Macro ' sort by column D (Sequence #) ' ' Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlToRight)).Select
First of all, I think this code can probably be simplified. Secondly, it does not work in older versions of Excel. In 2007 it always ends in a runtime error. How can I clean this up so that it will work in both versions of Excel?
Came across a spreadsheet that my firm created, where you can click on the column header and it automatically sorts ascending order for the column without messing up the other columns. Then if you double click it again, it will sort in reverse order.
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String With CreateObject("VBScript.RegExp") .Pattern = "reg_d+(_)+d+//d" GetString = .execute(txt)(0) End With End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
I have a filter on the Qty column but when I sort Z -A or A - Z it does not work. I have tried formatting as number but no success, I have never seen this before, why the sort wont work? I am using Excel 2010 in Win 7
I'm using Excel 2010). I have column I, which is the sum of columns A and B (first and last names of patrons), and I have used conditional formatting to identify all duplicates in column I with red text. I would now like a macro to find red text in column I and then delete the corresponding rows.
I've been looking around on the web for a while and have cobbled together this code stolen from other people's macros:
When I run it, I get the following error:
Run-time error '1004':Application-defined or object-defined error
Excel doc is called "stupid mailing list.xlsm", sheet I want to run the macro on is called "voters".
Highlighed the Column and Row of the selected Cell. I have seen someone using it. Whenever he click on any cell the related column and Rows clour change. For Example if I select Cell B5. The Column B and Row 5 change its colur from B1 to B5 and Row from Column A to Column B. If this option is already available in office 2010
I am using 2010 and want to select a range starting at the same cell all of the time (regardless of whether or not it has contents - so let's say B7. Starting at B7, I want the range to include all of the columns starting at B and go to the end of all of the columns. Then I want the range to include all of the rows starting at B7 and end at the last row. I will eventually copy this range and paste it on another worksheet start on a specific cell of another worksheet. (Perhaps, we can add a name to this range).
I have 5 worksheets that I currently have to add information to and cut/paste information from one to another. The initial information stays the same but I have to cut and paste it into one of the other worksheets based on wether or not we; need to decide on a job, are working on the job, lost the job, won the job or the job is complete.
I would like to create one master worksheet where the information can be entered with a drop down cell stating the status of the job (listed above). I created the master tab and linked the 5 subordinate tabs using an IF formula, but how can the subordinate tabs filter or sort the references and organize them on the top of the page rather than leaving a bunch of blank rows (because the info in these blank rows went to a different subordinate tab)? I want to enter the info in the master tab and simply change the pull down cell to change which subordinate tab the information shows up on. This should allow me to print the subordinate tabs as reports without having to manually cut and paste the info or filter it, correct?
I am trying to sort each "Pct" column in descending order. Of course, I can do this manually, but I have over 100 to do, so I'd like to know how I can automate this (of course, the two columns to the left of "Pct" must move along with it).
I've only recently began to use excel, but I've really dived deep into it, I'm clueless when it comes to VBA but now I'm stepping into that realm. Anyway, I'm using this code....
Private Sub Worksheet_Change(ByVal Target As Range)If Not Intersect(Target, Range("A:A")) Is Nothing Then Application.EnableEvents = False Me.Sort.SortFields.Clear Me.Sort.SortFields.Add Key:=Range("A:A"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With Me.Sort
Credits to VOG
Link: auto-sort for excel 2010.... to auto sort 2 columns of data. However, it is extremely tedious to enter data as it sorts itself every time you enter a new data point. Moreover, using any random number generator to fill the columns takes ages! Is there anyway to make it so the code only runs when the worksheet is refreshed (F9 pressed.)
I have a macro that is copying information from one spreadsheet into columns "A through E" on the next available row in a seperate spreadsheet. Beginning with column "G through L" I need to copy the formulas from the row above in the same spreadsheet. Is there a way to do that since the row being copied from will change each time?
I'm using excel 2010 and windows XP with a moderate amount of experience tinkering with macro programming. I know what I need is very doable but I can't get my head around what the code would look like. I must not be wording my searches correctly because most of what I'm getting for results are iterative programs based on a cells value which isn't what I need.
I'm trying to build a macro that will check a cell (C3) and based on the contents of that cell copy a column (I) to one of 12 different columns (K:V). So if the value in C3 is 1 it should copy I to K, if the value is 2 it should copy I to L, and so on.
I have an tab with 20,666 rows that I need to separate into different tabs based on what a cell in a specific row starts with.
Data Tab 001-020-002-000 Walker River Irrigation DistPO Box 820 YeringtonNV89447 001-020-005-000 Walker River Irrigation DistPO Box 820 YeringtonNV89447 001-020-006-000 Walker River Irrigation DistPO Box 820 YeringtonNV89447 001-020-007-000 Walker River Irrigation DistPO Box 820 YeringtonNV89447 001-020-008-000 Walker River Irrigation DistPO Box 820 YeringtonNV89447
So what I am looking to do is search the APN row in the Data Tab that starts with the number from the APN # row in the APN Tab and then copy the row to a new/existing tab named after the Description on the APN Tab.
I am currently using a pop up calendar in Excel 2010 that with CTL+SHIFT+B that a calendar pops up, you select your date, and then the calendar closes. What I now need is to write a code or formula or something that when I click in any cell in column F lets say that the calendar opens automatically in that cell and then still close automatically after the date selection is made.
I work on an excel spreadsheet all day and I'm constantly cuting and pasting a value to filter another column. I would love to have a macro button that would automatically do this.
The job card sheets are labled 'Page 1' through to 'Page 175'. As I am working on each job card sheet I need to filter column on another sheet within the same workbook. This is indicated on the attached picture.
So in a perfect world as I would enter in the PLANT ID number on to any job card, then hit a macro button on the toolbar and the Pole No column would then be filtered by the value in the PLANT ID cell.
As there are multiple job cards the macro would probably need to use the 'active cell' value to filter by. But as you can probably tell I'm no expert so you judge the best way to do it.
MWTS034G22 Job Card Sheet [URL] ..... Windows 7 (32bit) MS EXCEL 2010
I have a file which has in excess of 12,000 rows of data in 5 columns (sample file attached with fake data). The five rows are:
"First Name" "Last Name" "Name" (uses CONCATENATE to combine column A & B) "Email" "Date Attended"
There will be duplications in the list as people will have attended more than once over the years. What I want to do is search through the email addresses (Column D with D1 being the header) and where there is a duplicate email address copy the cell to the right of the duplicate (F#) into the next available cell to the right of the first occurance and then delete the row with the duplicated email address.
Attached is an example page. My goal is to be able to click on one of the header cells, and have it sort that column from high to low, and if I click that same cell have it sort from low to high. I know how to do this by making a command button, but, I want to do it by clicking on the cell itself. I just dont know how to do the onclick to execute the code.