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:
VB:
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
[Code]...
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?
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?
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 setup a table with a column titled "Type". Each value under column "Type" defines the type of row it is: "Section", "SubSection", "SubSubSection"
I wish to create a set of conditional formatting rules that apply to data rows in this table, each rule controlling how cells within a whole row should be formatted, according to the value found in "Type", for that row.
Example:
Rule1: [@Type]="Section" -> fat red line on top of cell. Rule2: [@Type]="SubSection" -> thin black line on top of cell. Rule3: [@Type]="SubSubSection" -> dashed black line on top of cell.
I can't seem to make this work.
How can I leverage the nice column names, and the "@"-this row designator, within a table to create conditional formatting rules that apply to all the cells within a row, in the same way one can refer to in table formulas?
I have a spread sheet which totals the amounts in 2 columns D and E, which are pounds and pence. The formulas for each one are:
Pounds
=SUM(D3:D8)+ROUNDDOWN((SUM(E3:E8)/100),0)
Pence
=MOD(SUM(E3:E8),100)
These work very well and give me the correct figures.
What I would like to do is to add up the values of each row based on the criteria of column C and have just one value in ONE cell (as shown on lines 13 & 14), so for instance:
When added together the values of rows 3,4 & 6 are 7.25 (based on a value of "W")When added together the values of rows 7 & 8 are 5.00 (based on a value of "F")
I have tried to show this as an example in the image below:
What is the VBA code for inserting text in all column B-cells of multiple selected rows?
I am creating a worksheet with a table containing various data related to orders of various materials (this is more or less data gathering from an older, paper-based 'system'). This table spans, columnwise, from A to D and expands downwards as more orders are added. The information in each column is: A=order number, B=type of material and C=material specs. and D=additional comments.
I've set it up so that the only thing I really have to do is to insert the type of material in the cells of column B, and the rest will sort itself out. Instead of having to insert a new row for each new entry and manually typing in the name of the material (these entries are often done in the midst of already existing data), I created several similar, macroed buttons for the different types of materials we use. These macros work by selecting the row of the currently active cell, inserting a new row and then add the name of the material in the column B-cell of this new row. What I am having trouble doing though, is to get the text-entry to work for a selection of multiple cells.
As an example, lets say that I would like to add 5 orders of "Grade A Steel" in the middle of the table - in the row above row 8. With the macro I currently have I can select cell B5, click the macro, and a new row will be inserted with "Grade A Steel" in column B of this new row. This action could be performed 5 times over, but would be easier if I could just mark a range of 5 cells, say B8:B12, click the macro and get the text/data inserted the column B-cells of all 5 of the new rows. So far I've been able to create a macro that inserts multiple new rows, but I've only been successful in filling the column B-cell in the first row leaving the 4 below empty.
I have several long lists (each is 600+) of vocabulary words & definitions and need to alphabetize/sort the words, but need to keep the definitions w/ the words.
A1 = word 1 A2 = def. 1 A3 = word 2 A4 = def. 2 A5 = word 3 A6 = def. 3
Standard sort will alphabetize all rows & the words will be separated from the definitions. I could group rows 1 & 2 together, rows 3 & 4 together, etc. but that's clunky & time-consuming.
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 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....
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
[Code].....
Code:
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.)
calculate the number of full rows in one data base located on one sheet X to determine how many rows the macro needs to extend on sheet B (sheet B is made only of formulas for data interpretation in sheet A.
I believe that the best way to evaluate this request is to look at the example. I have 2 buttons to demonstrate what I need, along with written instructions.
Running excel 2003 and have a number of sheets where i want buttons along the headings to sort a selection of rows.
For Eaxample one sheet i have data in range A6:AD135. i would like to assign a macro to various buttons on row 5 to sort by that column ie button in column b sorts all data in the range by column B.
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.
reg_[0-9]+_+[0-9]+/d
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 company asset data listed by item on wksht 1. Example:
Company A Computer 200 Company C Coffee Machine 21 Company A Car A123 Company B Computer 70 Company A Chair B14 Company B Desk C56 Company C Computer 59 Company C Desk C789
I want to sort and covert the column data on wksht1 to row data on wksht2. Example:
Company A Computer 200, Car A123, Chair B14 Company B Computer 70, Desk C56 Company C Computer 59, Coffee Machine 21, Desk C789
I'd like to do this via VBA/Macro(s) if possible.
I have to do this every month, so it would be nice to append the existing data to an "archive" file on wksht3 and then create this months data.
The data is imported each month via a text file with comma delimited fields which become the column data in the top example.
I am working in Excel 2010 on a windows 7 machine.
I have a years worth of data by day. I want to sum the values for each week.
For example, Cell B1 is: =sum(a1:a:7) Cell B2 is: =sum(a8:a14) Cell b3 is: =sum(a15:a21)
Is there a formula I can write that will increment the numerical portion of the cell by 7? If I drag down cell B1, the resulting formula is: =sum(a2:a8)
I know that I can use either =sumifs or =sumproduct to compare the dates and calculate based on that.
I tried: =sum(a1+7:a7+7) but that returns an error.
Again, don't propose solutions with either =sumifs or =sumproduct since I can already write those formulas.
I'm on mac Excel 2010 and i'm trying to delete rows..but for some reason they don't go! My workbook is a basic one, just filled with a bunch of formulas, i can't understand why it wont let me delete it.
When i do try to delete it, nothing comes happens and when i try to delete a large amount it comes up with 'not enough memory, continue without undo deleting rows' but my laptop has 8GB and my other laptop has 16GB so i can't see why a 16GB laptop wouldn't be able to delete it.
My business I work for is a collection agency and from time to time our clients send us files that are beyond recognizable for our collection software to understand nonetheless import into our database.
The file was a PDF at first but I have since been able to convert to excel format.
The data isn't too scrambled just scattered throughout rows. I would like to have rows of patient data into one row.
The file after being converted was all in row A but I have since used text to columns to divide into separate rows. However now one patient information is on multiple rows and I need each patient info on one row.
The file looks like:
|A---------|B-------|C----------| 1.John-----|Doe-----|123 Main St| 2.New York-|NY------| 3.11100----|SSN----|Balance Owed|
I would like the data from multiple rows to continue on row 1 like this:
|A--------|B-----|C------------|D----------|E--|F-----|G----|H-------------| 1.John----|Doe---|123 Main St.-|New York---|NY-|11100|SSN-|Balance Owed--|
I am no programmer in the least and have very little to no experience in pivot tables.
This file has about 7000 accounts with patient info spread through 3-4 rows for each set of data.
How I could go about doing this without manually copying and pasting into additional columns on the same row. That will take hours if not days.
I have dedicated rows and column units.I do not want these to be changed from there current setting.Can just rows and columns be locked and password protected? If yes,where and how do i process this feature?
I'm looking for a formula in column F that will return True if there are any negatives or blanks in E for any of that item A? False if variance is blank or all off that item has no variance?
