Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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

etc..

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

Sponsored Links:

Related Forum Messages:
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.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
Hide Contents Of A Cell But Not Remove In A Sort
Is there a way to hide the content of a cell that already contains a formula but when you sort it and remove the blanks it not remove that cell?

View Replies!   View Related
Pass The Contents Of A Listbox To A Function To Sort It Into Alphabeticalorder
I'm trying to do is pass the contents of a listbox to a function to sort it into alphabeticalorder. I get a error saying object required

Sub PopulateAll()
Dim lgLastRow As Long
Dim lgRowCounter As Long
Dim intCounter As Integer
Dim olb As MSForms.ListBox

For intCounter = 0 To UBound(vAllEnv)
Me.lstAll.AddItem (vAllEnv(intCounter))

Next intCounter

Set olb = frmOptions.lstAll.........................


View Replies!   View Related
How Do I Select & Use Contents Of First Non-blank Cell In Row
I've got 12 columns (headed Jan - Dec), each of which contains a combination
of numerical & blank cells. For each row, I want to select the first
non-blank cell and return the column header that it lies in e.g. Row 1, first
non-blank cell is in the Apr column, so I want the text "Apr" to be returned
to another cell.

View Replies!   View Related
Sort / Filter Rows: Sort A Range Of Row
I would like to ask if it is possible to sort a range of row? What I mean is, if I sort like Row 1, the entire block of row 1 will move as well? Like if i have column A to F, then row 1 of column A to F will move together at the same time.

View Replies!   View Related
Range Object To Essentially Select An Area Of Cells Whose Contents I Want Cleared
I'm having a problem in a single line of code in which I'm using a Range Object to essentially select an area of cells whose contents I want cleared.

Here is the
Sheets(Left(c.Value, Len(c.Value) - 1)).Range(Cells(4, 2), " &:& ", Cells(Rows.Count, "b").End(xlUp).Offset(0, 9).Select).ClearContents

I want the range part to evaluate as (b4:whatever the last cell is before the first blank row).


View Replies!   View Related
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.

Sub Sort()
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..................

View Replies!   View Related
Delete Rows Missing Contents In AF Then A
If a row in Column AF does not say “Cust” than I want to delete it, next if a row in Column A does not say “Numb” I want to delete it.

View Replies!   View Related
Sum Contents From Column Without Including Hidden Rows
Which formula should I use if I want to sum a column without including content from hide rows in the same column.

a2 is hided so I need the sum = 6

1
9
1
2
2

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
Hide A Rows/columns Based On A Cells Contents
way to automatically hide a row or column based on a specified cell.

Or even something general like: Hide all rows with cells evaluating to #n/a.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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
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.


View Replies!   View Related
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.

View Replies!   View Related
Trying To Sort Groups Of Rows
Range("C"&((h-1)*6+2)"G"&((h-1)*6+7)).Select

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

View Replies!   View Related
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
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.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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).
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"

View Replies!   View Related
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

Value1X1,X2,X5
I need it to change to this
Value1X1
Value1X2
Value1X5

View Replies!   View Related
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
A2 Name
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...

View Replies!   View Related
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

View Replies!   View Related
Select Rows Containing A Set Value
to be able to select every row, 1-3000, containing a value that will be typed into b2.

In other words, say I type 213 in b2, I need each row that contains 213 to be selected so that I can use those rows in another macro I have running.

View Replies!   View Related
Select X Rows
10.000 Rows have full of numbers in Cells.I have a Macro for each row.But I want to use this Macro for all Rows automatically.

View Replies!   View Related
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

Range("c6").Select
Selection.End(xlToRight).Select

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

Range(Selection, Selection.End(xlDown)).Select

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

View Replies!   View Related
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.

View Replies!   View Related
Sort Repetitive Data From Rows To Columns
I have a column of repetitive data:

BLANK CELL
NAME
ADDRESS
CITY, STATE ZIP
BLANK CELL

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
CELL

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?

View Replies!   View Related
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".



View Replies!   View Related
Sort Rows By Mid() String Of Column Cells
I have a single column dynamic range called "Dock" and I need to sort the rows in my worksheet based on information from this range.

Each cell in "Dock" is formated similarly to "TM09030010"
This is "TM" & YYMM & "4 digit number"

I need to sort my rows based first on Date "YYMM" then second by the last 4 digits of the same string.

The worksheet has information in columns A through K and Range "Dock" is located within column A.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
Only Print/select Certain Rows
I have a very large worksheet with many dates entered. I want to be able to run a macro that prints all the rows that has a date less that 35 days ahead in the future. all the dates are mixed up and not in any order.

Is there a way i can do this?

View Replies!   View Related
Select Rows And Export
I have an excel sheet that contains different partnumbers, i have them sorted a-z in column D.

I want to select all rows that contain the same partnumber and copy them to a new workbook. And repeat this action for all partnumbers.


View Replies!   View Related
Select Every Other Row :: Alternate Rows
Im trying to find a way select several rows at the same time but starting at say row 3 and then alternate rows so rows 3,5,7,9 etc

View Replies!   View Related
Select All Rows In A Macro
I need to create a new macro that will select all rows that have data and create a (3) pivot tables.

I have tried to create macros that will create a new tab and populate the pivot table. I keep getting an error that says "subscript out of range", so instead of creating the tab during the macro I have created the tabs as part of the template.

Again the problem is when running the macro with more rows, the macro doesn't recognize the additional rows. Ctrl + A is used during the macro but is hard coated for only the number of rows it selects, Ctrl + A twice is giving me "blank" data in the pivot tables. I have attached a sample sheet with 14 rows. Next month there may be only 10 rows or 50 rows. I have macros created for the pivot tables to view.

1: How can I create a macro to select all rows with data?
2: Can I create 1 Macro to create all 3 of the pivot tables needed?
3: Can the macro also create the tabs during the execution of the macro?

View Replies!   View Related
Select And Copy Many Rows
I need code that will look for a number in cell C1 and then it starts at C9
selecting cells to copy until it sees a cell with a number larger than what is in C1 and stops.

i need code for this since the number in C1 is always changing

Example: if there is an 18 in C1 it selects all cells that are >0 and <19

these cells are always sorted 123456........36 they are not random in order like 1 17 6 12 18 4 3

View Replies!   View Related
Printing Select Rows Only
I want to be able to print individual rows from my sheet. When I try, they run onto a number of pages. The information needs to be printed along with the column headers to make sense. Is there a way I can do this and get the rows to 'wrap' to save paper?

View Replies!   View Related
Select Rows Using A Variable
I am trying to select rows that contain text using a variable counting number of rows so rNumb = count number of rows

The Problem
Worksheets("Table").Rows("2:rNumb").Copy

rNumb won't work. What is the proper syntax?

View Replies!   View Related
Select All Rows To Hide Them
How do I select all the Columns and Rows that are not being used so that I can hide them. I like the blue background it gives when they're all hidden. I am using Excel 2007 and it's not too bad selecting all the columns but there are just way too many rows.

View Replies!   View Related
Non Consecutive Rows Sort Data With Multiple Ranges
Once again, I'm seeking some further assistance to an issue that I have yet to resolve.

I have several rows of data which I'd like to be able to sort by the info in column "A".

The data rows are not all consecutive. The range for which the data resides are:

Range("A25:A73,A85:A105,A145:A193"), When I sort I want my data to stay within these limits/ranges.

View Replies!   View Related
Sort & Re-write Column Data To Rows - 2003
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.

View Replies!   View Related
Select Rows By Group For Sorting
I HAVE THIS CODE THAT SELECTS ALL THE ROWS THAT CONTAINS CORTAR IN "I" COLUMN


View Replies!   View Related
Select All Rows With Same Date In Column H
I have a history worksheet which itemises all stock movements and enters all the movements on the next empty row as they are processed.

The transaction date for each movement is recorded in column H.

How can I select all rows with the same date in column H bearing in mind that there will be different numbers of rows each day?

View Replies!   View Related
Macro To Select Only Rows With A Value In A Range
I'm in charge of taking a report; streamlining its functions; and automating future reporting.

I'm using a database and some of the reporting function Access has. For another variation I'm forced to export the query to excel and transpose the vertical layout horizontally.

I'm making the entire process automated via a macro. The code is too long to post in its entirety but here is an example of what I'm trying to accomplish:

View Replies!   View Related
Find, Select, And Cutting Rows With VBA
I would like to do a find in a column for “yes,” and if it is True I want it to cut the row where it was found as well as the one above it to Sheet2. I need it to loop through the sheet appending to the ones that were found previously in Sheet2.

View Replies!   View Related
Select Rows When A Cell Is Not Empty
I would like to select rows of a range (eg A7:D11) but only those rows where the cell in column C is not empty

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved