Select And Sort In List By Header

Jul 25, 2007

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 2 Replies


ADVERTISEMENT

Create List With 2 Or More Same Subject/Header For Sort

Feb 25, 2009

I would like to create a list covering multiple columns.
I would like to have 5 columns which will contain the same value/ (word).
If I select,that value from the list, I would like to have each row the word is
found be displayed regardless of which row it is found.

View 7 Replies View Related

Sort From Onclick Of Header

Jan 17, 2010

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.

View 3 Replies View Related

Data Sort By Header

Jul 19, 2007

what is the code to sort a spreadsheet in VBA code by HEADER rows?

i have 2 rows i want to sort by: first by Column D row 1, then by column C row 1.

Cells.Select
Range("C1").Activate
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("C2").Select

selects all cells, sorts, then de-selects.

View 3 Replies View Related

Column Header As Sort Tool

Apr 14, 2008

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.

View 9 Replies View Related

Sort Data With Two Header Rows

Apr 30, 2008

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

Sort Data By Clicking On Header Row Cells

Oct 7, 2009

In the worksheet "Batting and Pitching Register", I'd like to be able to sort the data in the two charts (APBA Batting Register and APBA Pitching Register) by simply clicking on the cells in the respective header rows.

I've set up ranges (Batting and Pitching), but have had no luck moving beyond that.

View 9 Replies View Related

Possible To Sort Hoizontally With Multiple Columns Under One Header

Aug 6, 2008

In row 1 I want to have the names of servers, so we would have A C D E

Under each of those I want to have 4 other columns, so A would have on row 2 Start, End, Data, Time, or something like that.

Then B would have under it Start, End, Data, Time

And so on for C D ...

I would then want to sort it by the top level row, so if I had to insert B at the end I could sort it so it would be

A B C D E with all of the Start End Data and Time for the server to be moved along with it's master header.

I tried setting this up but then I went to sort it told me it could only sort if the columns were the same size, so having a merged top level A with four things under it did not work.

View 9 Replies View Related

VBA - Select Filtered Rows Then Copy All But Header Row

Feb 12, 2014

I want to filter my dataset, select column B & C to copy, but exclude the header row. I am having a hard time offsetting the selected range. I get a compile error on what I try.Here is my code (yes, I know, its a little juvenile and I will clean it up, but I need to see things clearer at first):

Sub GetBarrelQualifiers()
'
' GetBarrelQualifiers

[code]....

View 5 Replies View Related

Macro To Select Range With Column Header?

Mar 12, 2003

How would a code look like to select a range with a column header on top. in the code you provided, it selected the entire W column. How would you select the range given that the W column was called Address?

Sheets("Sheet1").Select
Range("W1").Select
Range(Selection, Selection.End(xlDown)).Name = "Problem_Area"

View 6 Replies View Related

Rearrange (sort) Columns Based On Number In Column Header String

Apr 3, 2014

I want to rearrange(sort asscending) columns based on numerical value in column header string through VBA macro. Please check attachment.

i.e. (Present Data)
# A B C D
1 col.1 col.4 col.3 col.2

(Output Data )
# A B C D
1 col.1 col.2 col.3 col.4

test.bmp‎

View 2 Replies View Related

Macro To Sort And Align Employee Data In Multiple Cols To A Header Row

Jul 9, 2014

I have an issue with an export file which is produced in CSV format and needs to be aligned. I have attached a sample of what I refer to.

My Source excel file looks like:

ABC A-101 B - 202
DEF B-203 C - 100

The destination should align with the relevant column headers and place a blank if it doesn't match. Other have queried a similar thing, however my header and data and description is actually in the same cell with a dash separating them.

The output I want is:

A B C D
ABC 101 202
DEF 203 100

There are multiple row of employees with varied column headings as its dependent on what system access the user has. The headings i.e. A,B,C for example is a fixed number of headers.

In my real data set the headers represents a 3 letter system prefix e.g. ZCR,ILP etc

View 1 Replies View Related

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.

View 2 Replies View Related

Macro (Select, Sort And Select)

Nov 16, 2006

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

How To Differentiate Header And Values In List Box

May 21, 2014

how to differentiate Header & values in List Box.

View 8 Replies View Related

Search A List - Return Header / Value

Apr 5, 2004

I am trying to search a range for all of the nonblank cells, when I find one, return the column header and the value in that column. Vlookup won't work on this. It will only return a specified column.

Employee performance

ID area one | two | three

12947 23 | | 19
12948 32 | | 20
12948 |17 | 52

What I am trying to do is create a communication sheet. Employee ID 12947, your performance in area one= ______, in area two = ______ and so on.

View 3 Replies View Related

Creating A Vertical Header List

Mar 27, 2009

This is probably simple (I hope). But I'd like to create a list of Locations from a dynamic list on a separate sheet.

Sheet2!H2:H10000 has a list of Locations, with a LOT of repeats.
Sheet1!A2:A100 is where I'd like to list the locations, without repeats.

Sheet1!A2:A100 is also used for a summary (in column B) and as a Data Validation point (for a list in G1)

The table on Sheet2 can vary from 0 items to 10,000 items and the number of different possible Locations is about to expand as it's resulted from a query to an Access database with filters.

View 9 Replies View Related

Excel 2013 :: Can Sort List A-Z Then Numbers At Bottom Of List

Jun 5, 2014

Does excel 2013 have a fence way to sort a list of alpha numeric numbers and alphabetize in this order A - Z and then 0 - 9.

Ex) Apple, Greg, Rob, Sand, 123, 126, 1000, 2001

View 2 Replies View Related

VBA To Select Rows And Sort Contents

Dec 21, 2008

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

Programme To Automatically Select A Value From A List Or Range Once A User Has Selected Corresponding Value From A List Within A Combobox

Jan 28, 2007

i'm writing a refrigeration selection user interface, working from values on an excel spreadsheet. how to get the programme to automatically select a value from a list or range once a user has selected corresponding value from a list within a combobox. for example if a user sets the temperature of their refrigerator to -5 celsius i need the programme to automatically select the corresponding value of enthalpy for the air at that temperature.

View 5 Replies View Related

Link A Header Or A Portion Of A Header To A Specific Cell

Jun 23, 2008

Is there a way to link a header or a portion of a header to a specific cell?

View 14 Replies View Related

Return Column Header If Row Header And Value Is Known

Nov 18, 2011

I've been trying find an appropriate formula to extract the column header from a table in a different sheet if the row header and value in that table is known.

in the lookup table the row titles (column A) are product codes, column titles (row 2, D through AX) are business names and the table values are quantity.

In a different table I have product codes in column A and in column B i have the max number/quantity of products for that code. In column C i want to put the company name associated with the product and the number/quantity.

View 4 Replies View Related

Long List Won't Sort?

Jul 22, 2013

I am trying to sort a fairly long list of tasks into a custom calendar order. I have created the custom list as Sept, Oct, Nov, Dec, Jan etc through to Aug, with one additional heading of "Recurring". The list won't seem to sort by the column where these headings occur. See attachment.

View 7 Replies View Related

VBA To Sort Worksheets From A List

May 7, 2009

Is it possible to sort numerous worksheets based off a list of cells that are the names of the worksheets within the same file? For example my worksheet names are:

YTD Texas
YTD Florida
Period Texas
Period Florida

I can sort the sheets by alpha but it puts the two YTD worksheets together when I need the two Texas sheets side by side (I need this on a file that contains over 100 worksheets otherwise I would do it manually) I was wondering if I could create my order of sheet name in another worksheet and reference that list through vba code?

View 7 Replies View Related

Getting A List To Automatically Sort

Oct 17, 2013

I am trying to get a list to sort automatically. I am running a competition over an 11 week period and entering weekly data that is totalled on a separate "Totals" sheet.

This "Totals" sheet, in turn, feeds a "Scores" sheet which I want to automatically sort in descending order of score in order to show the current positions in the competition.

The data in the list isn't entered directly but comes from formulas that reference cells in other workbooks.

I have found ways of sorting a list when you enter data into the list itself but what I need is a macro that will update the sort order automatically whenever a new value is entered in one of the other workbooks.

View 1 Replies View Related

SORT AN ADDRESS LIST

Jun 3, 2006

I have a wedding list of names and addresses in one long column. The names occur every 5th line. They are not in alphabetical order.

View 9 Replies View Related

Sort List By No Of Characters

Feb 18, 2007

I have a large list of phrases, about 30,000 all in Col A. (From A3 to be exact)

What I want is to sort by number of characters.
I know excel can sort A-Z,,, but I can't see by number of characters??

Is there anyway I can sort this list by No of Characters please??

List does include letters and numbers.
But they are mainly 3-4 keyword phrases.

This is so from a large list all the least amount of characters will be at the top of the list so I can easily see them,, and probably delete most of the 1,2,3,4 character returned results.

View 9 Replies View Related

Sort Based On List

Jun 4, 2008

sorry for restarting this thread but i started one similar on saturday and the person I was talking to has not yet responded

I want to know if it is possible to adjust this code line:

Case "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"

from the macro below to instead get the case names from a list on a worksheet... the reason for this is that the names of the worksheets I want sorted will change periodically.

Sub SortDaysoftheWeek()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"
With ws
.Activate
.Range("A6:G256").Sort Key1:=Range("A6"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Select
Next ws
End Sub

View 9 Replies View Related

Sort List With X Exceptions

Aug 22, 2007

I need to create a custom sort which will cause entries "TBD" and blank to appear at the bottom of my spreadsheet. Any other value will be sorted alphabetically. I'm not sure how to create my sort list to do this. I tried "*, TBD " but this did not work.

View 2 Replies View Related

Automatically Add To Validation List And Sort?

Sep 24, 2007

I have created a validation list which automatically adds any new values a user enters.

Is there any way of sorting the validation list every time a new value is added to the list?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved