VBA To Select Rows And Sort Contents
I'm trying to sort a whole list of data that's been separated into blocks with a row containing the value "a" like:
row 9 a
row 10 455
row 11 8892
row 12 34
row 13 a
row 14 578
row 15 92
row 16 a
I want to sort each block into ascending order using VBA code and then add in some extra code to do some more analysis.
The data to be sorted is in Column D, and there are also values in Column C and B that need to be sorted with the Column D values (so that they stay in the same rows).
View Complete Thread with Replies
Related Forum Messages:
Vba Select Rows Based On Info In A Column, Then Paste Into New Workbook
I'm using excel 2003. I would like to select and copy rows of data within a worksheet of a workbook and then paste those rows into a newly created excel workbook. That is the easy part. The complication is that I would like the macro to select the rows based on a particular string value that will be impossible to specify in a fixed way (i.e., it will change from project to project). Let's say the variable that distinguishes the rows is location, so some are from Tokyo, some New York, and some from Paris. Using vba, how can one 'splice' the data into (in this case, three) seperate workbooks based on the string/values of another variable?
I am familiar with auto-filter, but in this case, I really do need to create seperate workbooks, rather than apply calculations to a selection of the data.
Macro (Select, Sort And Select)
I need to get a macro to select all the data in column "A", sort it in ascending order, omit the blanks if any, then select (highlight) all the data so that another macro can be run.
When I record it, it will only record up to the last row I highlight but the data always changes so there could be more or less.
Clear Contents, Copy And Sort
I have 10 separate files that each have a data input sheet. I have 1 master file that has all 10 sheets in it and an extra sheet to display all 10 sheets information on 1. The drama I am having with sorting the master sheet is that every cell in the 10 sheets has a formular to copy what in the corresponding cell within the separate file. So techincally, the cells are not blank.
The cells range is B5:G1004 on every sheet. I paste 'values only' from the range of each sheet into the master sheet, but when I go to sort column B Ascendingly, it puts all the data at the bottom of the rows and there is 10000 rows on the master sheet. I cant get it to flip around. What I need is a way to clear the cells on the 10 sheets that do not have a value before I paste into the master sheet.
Sort Data By Comment Contents / VB Macro
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.
Select And Sort In List By Header
I have a spreadsheet which contains a list of chemicals in Column A, and a list of numbers in Column B. The chemicals are separated into groups. I am trying to create a macro which sorts the chemicals in their groups by column B, so that the lowest number is at the top. I have 8 groups, and the number of lines in each group may vary, so I need to make the code dynamic.
I have attempted it. The way I thought it could be done was to search for the first group header, then save the address. Do the same for the next group header, then offset that by -1 row and 1 column, then sort using these two addresses as the range. The first part of the code works, where I find the address for both group headers, but everything after that doesn't work.
Dim CNMT(8) As String
Dim j As Integer
Dim fromRow As String
Dim toRow As String
Dim rng1 As Range
Dim rng2 As Range
CNMT(1) = "TPH Fractions"
CNMT(2) = "BTEX & MTBE"
CNMT(3) = "PAHs"
CNMT(4) = "VOCs"
CNMT(5) = "SVOCs"
CNMT(6) = "Metals"
CNMT(7) = "Inorganics"
CNMT(8) = "Pesticides"
For j = 1 To 8..................
VBA Code To Sort Data - Run-time Error When No Data To Sort
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.
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
Range("AA11:AA" & zCTR).Select
Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
VBA Clear Contents
i am trying to use clearcontents with the below code but i get an error,
.ClearContents Range("J7:J" & .Range("A" & .Rows.Count).End(xlUp).Row)
.Formula = "=H7-I7"
.AutoFill Range("J7:J" & .Range("A" & .Rows.Count).End(xlUp).Row)
Delete Rows Based On Cell Contents
I have merged two workbooks into one. What I need to do at this point is to delete all rows that have a duplicate entry, basically anytime the cell content in one cell matches the cell content in the cell right below or above it, BOTH rows should be deleted. At this point, this is above my VBA skills so I'm asking for help in how to do this. The stripped version of the workbook is attached (only 100 rows) but in reality this is a huge workbook with almost 22,000 rows.
You will notice in the attached workbook, that cell contents for A2 and A3 match. For what I need to do, I need both rows (2 and 3) to be deleted. If you go down a bit, starting in row 89 all the contents in column A are unique so those need to remain.
Vba Clear Contents Of Column
I dont know how to copy an excel sheet into the question.
But assuming the following text starts at CELL C2 (this is just a section of the workbook)
C D E F G
This is a worksheet to test multiplication tables. I am trying to create a macro button to clear the contents of column F, for the kids to start again?
Macro - Format Rows Based On Cell Contents
I have a spreadsheet that has a subtotal row after each data grouping. I need help with a macro that will go to column C, look for any cells with the word "Subtotals" in it, and format that row based on another macro I already have set up. I've tried about 50 different types of macros that I found out on the web, but none have worked.
Move Cell Contents & Delete Rows
I have a spreadsheet with a serial number is row 1 in column A with the rest of Row 1 empty. Column A is empty in Row 2, but has the data associated with the row above in columns B through L of Row 2. Then comes 2 blank rows and the pattern repeats with a new serial number in column A of row 5 and so on. I would like to move the serial number down one row, delete the now empty first row, delete the following 2 empty rows, and then loop to do the same thing again for all 9000 rows of the spreadsheet.
VBA With Range() To TRIM() Cell Contents
I have column headings that come in through a file upload and for some reason some of the column headings(which are in cells in row1) have extra spaces in the middle of them and at the end of the word. This messes up some of my later coding when trying to find specific header names.
"Location " - has extra 2 spaces at end, should be "Location"
"Read (Only)" - has 3 spaces in middle, should be "Read (Only)"
I came up with this piece of code to quickly correct the bad headers by Trim()'g the contents of each cell, but it's error'g out & not sure how to fix it:
.Value = Trim(.Value)
It errors of course on the '.Value' line.
Vba Cell Control Based On The Cells Contents
Is it possible to place formulas in vba to control cells based on the cells contents.
A2 will be the the price of an item excluding vat and b2 will be the price of an item including vat
What i want to achieve is if I put a figure into a2 b2 then multiplies a2 by 1.15 to reflect the price including vat. If this is achievable I then want to reverse the proceedure so if I put the price including VAT into b2 a2 divides b2 by 1.15 to reflect the price exc. VAT.#
if all of the above is clear and possible I would like to know if it is easily possible to copy the workings for a3, 4 ,5 ,6 etc and b3, 4, 5, 6 etc.
VBA - Userform: Click In A Text Box, Contents Is Selected
I'm building a userform (for the first time), and I'm working with a textbox control. I'd like to make it so that when the user clicks in that textbox, the contents is selected, so that if they begin typing, it will type over what is already there.
My question, specifically, is whether or not there is a property I can set to make this the default behavior, and if not - what event do I tie to this action? Is it the "Enter" event?
What I have now, which will probably be unacceptable to my users, is code that looks like this:
Private Sub tbName_Enter()
ActiveControl.Value = ""
This makes the contents disappear, but I'd rather they stay there and just become "selected".
Sort Rows Without Sorting
I have data being fed from an application where it's difficult to sort the rows into ascending order. So is there a way that VBA can sort them and produce a value according to the order?
For example, Column A contains 5 rows.
Row 1's value is 4
Row 2's value is 1
Row 3's value is 2
Row 4's value is 3
Row 5's value is 5
If I were to sort these into ascending order, I'd get 1, 2, 3, 4, 5 in Column A. But I can't sort the rows so I need code to tell me which is the lowest number (1), which is the second lowest number (2), etc.
The purpose of the code will be to put values in Column B according to what's found in Column A. For the the lowest number, 1, the value in the cell next to it in Column B would be "a". For number 2, it would be "b", etc.
Trying To Sort Groups Of Rows
What is the proper name convention if I want to sort a group of 6 rows at a time. For example if h =1 then I want to select from C2:G7 to sort but I am getting an error when I try to input it as above. I have the sorting code, just need it selected first.
Sorry, I don't know how to turn off smiley faces because the options list isn't available to me, but before the G is a : and a (
Trying to make it Range("C2:G7").Select
Macro To Sort The Rows
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
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.
Sort The Rows Of Data
We have an issue with sorting rows of data that have formulas in them.
These formulas are using data from a row which is above the column headers.
For example cell H2 has 160 manually entered in it. It's the number of work hours available in January 2009.
The column header for that column is in Q4 and has a value of 'Jan'
Below this in cell H5 is the formula '=+H2' because the resource in this row is available for 100% of the month.
Another resource in row 22 has the formula '=+H2*0.15' in cell H22 because they are only available 15% of the time that month.
The problem occurs when we try to sort this data. Most of the cells displays #REF!. Some show a value, but the calculation is wrong.
Sort Rows According To A Variable
I have the following code which sorts rows accoridng to a variable retrieved from a work sheet. When I plug in "SPY" (name of one of the variables in column B), it works fine, but with variable stock_symbol, I'm given an error for some reason.
Sort Without Using The Top 2 Rows
I have a sheet that has some hidden calculations in the top 2 rows. My actual headers start in row 3 (the amount of data always changes so I chose to put the hidden constants at the top rather than the bottom). I want to sort by the headers in row 3 but I can't figure out how.
I need some code to attach to a button that will sort data in H3:CA770 in descending order, the catch is I need this to sort on by the column to the most right with data in it.
I use rows 1 & 2 as a header so there will always be data in that, so I need the button to sort my worksheet based on the last column with data in it. The sheet will be updated monthly so I dont want to creat 12 different sort buttons.
Macro To Delete Rows And Sort
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).
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"
Sort Data Into Multiple Rows
I'm trying to merge 2 spreadsheets togeather but befor I can do this I need to resolve the following issue - my Excel worksheet has 2 columns with data that looks like this
I need it to change to this
Sort Data With Two Header Rows
I have two header rows on a sheet that I need to sort starting on Row 3, Column A.
The sheet is A-K and rows 1 and 2 are headers
Row 1 has merged cells of A-F which says "Information" and then G-K " Tracking Section"
Row 2 is broken up into individual sub-headers in columns, A-K each one having a bit more info for instance:
Under the "Information" merged header in row 1 there is
B2 Work Center
C2 C/O date
D2 Due CC
E2 Due MX
F2 Date Received
and G-K in row 2 has different sub-headings.
I have tried to use my normal code, which is great for one header, but it ends up sorting the sub-header as I call it...
Sort Unknown Number Of Rows
I am working with 2 workbooks the first one "LH Enrollment" is used to enter information for new students being enrolled in the program. After the information is entered the macro is then copying the information to the second workbook "LH Children Records". The part I'm stuck on is that I now need to sort alphabetically by last name which is entered on Sheet 1, Column B of "LH Children Records". The number of columns will stay the same but the number of rows will change each time a new child is added. Eventually all this information gets copied over to several other workbooks, but I'm stuck trying to sort. I have attached both workbooks
Using Sort Descending In VBA
I have some sheets which I would like to loop through and use sort descending on column S, would somebody help me out with the code to do so? I actually have the looping code already, I just need the sort descending bit.
VBA: Sort On Two Columns
I have the necessity to execute a macro for sorting a list of data on two columns.
More exactly, let's suppose to have this situation:
A 10/2/2008 FFFF GGGG HHH
B 01/3/2008 PPPP LLLLL NNNN
B 12/4/2008 XXXX JJJJJ PPPP
B 08/1/2008 HHHH SSSS IIIII
C 15/10/2008 AAA BBBB CCC
I need this sort:
A 10/2/2008 FFFF GGGG HHH
B 08/1/2008 HHHH SSSS IIIII
B 01/3/2008 PPPP LLLLL NNNN
B 12/4/2008 XXXX JJJJJ PPPP
C 15/10/2008 AAA BBBB CCC
Custom Sort Using VBA
I would like to custom sort a table by ID, the first ID to be shown on top would be the ID the user enters in an inputbox. Below is the code I used but I dont know why its not working:
Dim MyCount As Integer
MyCount = Application.CustomListCount + 1
MyValue = InputBox("Enter ID")
Key1:= Range("A1"), _
find attached an example, try entering 300000 in the inputbox the custom sort doesnt work
Select Last X Rows
I am trying to record / edit a macro in Excel Office 2003 that selects a cell (c6), then goes to the last cell to the right of that range which I am doing fine
What I do now is select from that last cell found in the range down to the last cell in that column and I am getting that to work OK
I now have a range of data on one column selected as the last column of data in a range. What I am having difficulty finding the solution to is how I can keep this range highlighted and include on the highlighted range the previous column of data as well (column to the left) - which would give me the last column of data (this months infomation so to speak) and the previous columns data as well (last months so to speak) - so I can then progress to copy paste to a selcted area - later I will add a new column of data so the last column and the previous one moves to the right every month so cannot select by specific cell references
Insert Blank Rows, Sort And Highlight
I am trying to accomplish three things with the attached workbook. The first thing is the insert a blank row between the different part numbers, column "A" to make the sheet easier to read. The second thing is to highlight the data in light gray (A:E) leaving the blank row that was created empty. The third is to then sort the worksheet by data in column "A" to group like numbers together.
Macro To Sort 100 000 Rows
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
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.
Sort Repetitive Data From Rows To Columns
I have a column of repetitive data:
CITY, STATE ZIP
I would like to be able to resort the dats so that each line becomes a column
BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK
I know I can do it 3 cells/rows at a time using the paste special and
transpose command but I have a 1000 names and addresses. How do I do it in
one operation instead of a thousand?
Macro To Sort Multiple Rows Of Data
I 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".
Sort Rows Housing Conditional Formatting
I have a spreadsheet in which I needed to add the row totals of cells affected by conditional formatting. Ozgrid came to the rescue here (kudos to Dave H and JimFuller1), but now if I sort by these totals the results do not match the rows they are associated with. The attached spreadsheet shows the values the users want to see on Sheet1. The user changes the highlighted values in the rows by changing the values in A1 and B1. These index a set of tables on Sheet3, and Sheet2 mimics the CF conditions in order to provide the totals in col V of Sheet1.
Now if I filter a Sheet1 field on nonblanks (to get rid of the blank rows) and then attempt 'Sort Descending' on Col V, all the rows appear to sort correctly with the exception of Col V which now appears to give the wrong total for the row.
Macro To Sort Rows By Date Order
Starting at E8 and going down the E column there is a list of non sequential dates that could potentially be very small or extremely large. These dates must be sorted in ascending order together with all the data in their respective rows so for example
John Ball 20/2/07
Mark Dunn 19/2/07
Tim Jones 18/2/07
should be sorted as
Tim Jones 18/2/07
Mark Dunn 19/2/07
John Ball 20/2/07
If anyone can write such a piece of code it'd be very time saving.
VBA Auto Sort The Column
I have the below small code which auto sorts information in column A & B but I am having trouble trying to get it to sort from row 3 and not sort the first 2 rows. The first row is the column header the second row I want left blank and then sort after that.
Plus at the moment after I enter something into column A it auto sorts itself but what I would really like to do is have it commence the auto sort after I have entered information in column B and pressed "enter". Because at the moment I enter info in column A then it auto sorts itself then I must then find it and then enter the info into column B.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rg As Range
Set rg = Columns("A") 'Column containing names to sort
If Intersect(Target, rg) Is Nothing Then Exit Sub
Range("A:B").Sort key1:=rg.Cells(1, 1), order1:=xlAscending, header:=xlGuess
Sort An Array In VBA Memory
The following is the VBA version of what i've been using:
Range("A1:J1000").Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:= _
Range("E2"), Order2:=xlAscending, Key3:=Range("F2"), Order3:=xlDescending _
, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
now arrays are bigger than 1000 rows>> even bigger than 65536.
Such an array is stored in "arrBIG" and dimed as (1 to 10, 1 to 100000). The goal is to sort it by the 4'th, then 5th then 6th values in the first dimension - the one that is 1 to 10 - i call this the column.
arrBIG.Sort Key1:=4, Order1:=xlAscending, Key2:= _
5, Order2:=xlAscending, Key3:=6, Order3:=xlDescending _.................
VBA Macro To Filter And Sort
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.