Sort Area Around Active Cell By Two Columns

Feb 6, 2008

I have various lists of data (using Data > List > Create List...) that do not always have the first cell in the list in column A. I have spent quite a bit of time searching the internet to try and find a macro that will work the same as... Data > Sort > Sort By: Col xx > Then by: Col xy.

Dave supplied me with a macro that will sort a range by the first column:

Sub SortThis()
With ActiveCell. CurrentRegion
.Sort Key1:=. Cells(1, 1), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
End Sub

I have recorded macro that does the same and looked at the code but this uses Range and specifies the cells to be sorted. I have not been able to work out how to modify the code Dave posted to do a minor sort by the second column. I am after a macro that will determine the range (could be a varying number of columns and rows in different places). For however many columns there may be the macro will sort the full width and height by the first column and then the second column, with all the data within a particular row not being split.

View 2 Replies


ADVERTISEMENT

VB Macro - Sort Data In Columns Within Active Spreadsheet?

Feb 12, 2014

I am new to VB Macro creation and I am creating VB Macro which will:

1. Sort data in Columns within an active spreadsheet

2. Create new Worksheets

3. Delete Values in rows based on value in Column.

I have listed my "Step by Step" instructions in the tblTest Excel file on attachment. The instructions are clear and straight to the point.

Below are some VB Macros I was experimenting with but it is not complete.

Sub Sort_Ascending_With_Header()
'Sorts a worksheet in ascending order and assumes there are headers on the data
Range("A1:DZ20000").Sort _

[Code].....

View 2 Replies View Related

Sort By Active Cell Column

Jan 7, 2007

I am trying to perform a sort based on the ActiveCell.Column

I thought my code would exclude the hearer rows, but presently it moves the header rows beneath the data

I tried
Header:=xlGuess as well as
Header:=xlNo

Same result

What am I doing wrong?
Thanks
-marc

Private Sub comp_mySort()

Selection.Sort Key1:= Cells(1, ActiveCell.Column), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

View 9 Replies View Related

Active Area

Dec 29, 2008

I have the following macro that works fine. But, I would like it to stop when it gets to the bottom of the active area.

Sub Insert_for_SQs()
Do
Selection.EntireRow.Insert
ActiveCell.Offset(6, 0).EntireRow.Activate
Selection.EntireRow.Insert
ActiveCell.Offset(12, 0).EntireRow.Activate
Loop
End Sub

View 9 Replies View Related

Macro To Publish Print Area From Active Sheet To Web Page

Feb 26, 2009

I am trying to write a macro that publishes the print area as a non-interactive web-page to a file that sits in the same location as the spreadsheet from which the macro is being run.

I am using the the following macro:

View 14 Replies View Related

Set Print Area Based On Sort

May 15, 2008

I have a worksheet which contains data for 6 different groups. I have a sort macro already that sorts them into groups (group 1, group 2, etc.) What I want to do know is set the Print Area so I can just print each group individually with a button("print group 1", etc.)

The worksheet is set up as follows:

Columns B-H are the data I want to have printed. The number of rows is different for each group. I want to set the Print Area based on Column H. So, if H=1, set the Print Area for Columns B-H and all rows that have H=1. This seems like it should be simple enough, but I haven't quite been able to put the pieces together yet.

View 10 Replies View Related

Macro To Print Active Area Excluding Cells With Forumula That Doesn't Return Value

Dec 20, 2007

Trying to put together a macro that looks down active sheet for all cells that contain a value, sets a print area and then prints !

Is this possible?

FYG, I have a column that run from 3 - 2000, which contains a formula, which may produce a value depending on corresponding cells.

I used this code from a post on a similar topic, but excel is complaining code
in bold

Private Sub Print_Area_Click()
Dim lastCell As Range
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0)
Do Until Application.Count(lastCell.EntireRow) 0
Set lastCell = lastCell.Offset(-1, 0)
Loop
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address
End Sub

View 9 Replies View Related

Range X Rows & X Columns From Active Cell

Mar 19, 2008

Im writing a macro and have a cell activated (using ActiveCell). If I want to select this cell (the activated one - i.e., k3) along with the 2 cells next to it (i.e., l3, and m3), how would i go about doing that?

I'm wanting to shift 3 cells downward and I know how to shift them down, but dont know how to select the cells i want.

View 5 Replies View Related

Hide Columns Starting From Active Cell

Apr 30, 2008

Continuing with a large Macro I am trying to peice together, I have reached a hurdle where I need to hide all columns to the left of the ActiveCell (which varies week to week) back to column B.

I have the following code which selects the correct number of cells to the left of the active cell but doesn't highlight the whole columns - just 3 cells for each column (very odd)

ActiveCell.Offset(0, -1).Range("A1").Select
Range(Selection, "B" & Selection.Column).Select

I'm sure it must be something to do with the

Selection.Column

part as when I write

Selection.Row

it just does 1 cell for each column (as to be expected)

I should mention I already have the coding to hide the columns, just need a way to highlight the relevant ones.

View 3 Replies View Related

Greyed-out Columns Based On Cell From Specific Column In Active Row Being Edited

Jul 7, 2014

I am making a spreadsheet in which I have been asked to have certain column greyed-out based on an answer that was given in a dropdown menu. Specifically, a dropdown menu in column D asks if the client was a Youth or an Adult. Depending on what the anwer is, other columns in the spreadsheet will be greyed-out; different ones for each answer. My problem is, I need this to occur for the active row being editted. For example: The first row that the user can input data into is row 4. If D4 is answered with "Adult" then certain other columns are greyed-out. However, if the next time the user updates the spreadsheet with information for a different client (now entering information in row 5) and they select "Youth" for column D5, then different columns get greyed-out. The columns that are greyed-out will depend upon the answer for the dropdown menu in column D for whatever row is actively being editted.

View 5 Replies View Related

Move And Sort With One Column But Insert Extra Columns As Needed For Proper Sort?

Jan 13, 2014

Using DataEntry sheet for data.
Trying to rearrange the data to DataFormatedProperly sheet.
So far all I can accomplish is DataFormatedWrong sheet.

Edit: Not sure what happened but file was NOT understandable before. It should be correct now.

View 2 Replies View Related

Sort Data On All Worksheets Active And Other

Aug 21, 2008

It sorts the ActiveSheet, but none of the other sheets and there's no runtime error. I am using this on a test workbook with the same data in 5 worksheets.

What's wrong with this code?

Sub SortSheets()

Dim ws As Worksheet

For Each ws In Worksheets

Cells.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Next ws

End Sub

This looping structure works for PageSetUp, but not this Sort.

View 5 Replies View Related

Sort Range On Non Active Sheet

Dec 22, 2007

I'm trying to sort data on sheet1 and sheet2. I'm running all macros from sheet one and I need it to stay active all of the time. I'm using the following code to sort sheet2. When I sort the column on sheet2 with the code, I can not get back to sheet one without an error.

Worksheets("Sheet2").Activate
Columns("a:a").Select
Selection.sort Key1:= Range("a1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

I've tried

Worksheets("Sheet1").Activate
Columns("a:a").Select..............

View 2 Replies View Related

Excel 2003 :: Highlight Cell On Same Row As Active.cell Whenever Active.cell Changes

Dec 10, 2012

whenever the active cell is within a given range, highlight the cell on the same row in column S (by changing its interior colour). This should occur each time the active cell is changed, whether by cursor keys or mouse. The effect would be similar to the row and column highlights at left and top of the worksheet.

This action should be restricted to one sheet in the workbook.

It's for Excel 2003.

View 3 Replies View Related

Selecting Range From Active Cell To Last Entry In Same Row As Active Cell

Jun 8, 2014

I have got stuck on one piece of my code and having trouble fixing... Overall I am trying to find variable station name in cell L2 of Sheet 2 in Sheet 1 and then select and copy the data from the data in "cell L2 of Sheet 2" to the last entry of that row. I have attached an example test spreadsheet of the data and a macro is within Sheet 1 called test1. Please note that cell L2 in Sheet 2 will always be different station name and the station list in Sheet 1 will change with differing station name.

The code I am using is:

[Code] .....

The code that is not working and bringing up an error is:

[Code] .....

Attached File : Copy of Testexample.xlsm‎

View 2 Replies View Related

Multiple Columns In Data Area At One Click?

May 31, 2014

I have data of all my retail stores around (550)(Store Names in columns) with Product description and Sale & current Inventory, it seems very tedious job to add all the 550 stores in data area so to get sale and stock value, is there any way that I can add all stores in data area in one go.

View 3 Replies View Related

Sort Horizontally. Sort Across Columns

Dec 30, 2006

I am trying to sort a long range of text that is placed horizontally in a spreadsheet. I can do it vertically with the sort function in Excel but it does not seem to work for text that is placed horizontally. Example is as below:

Inventory Accounts Human Resources

View 2 Replies View Related

Create Document With 3 And 4 Columns That Occupy Same Sized Area?

Apr 30, 2014

One of my coworkers is trying to create a document that has 3 columns in one part of the spreadsheet, but further down on the page, he needs to have 4 columns but wants them to use the same amount of space as the three columns. I have looked around and don't see how that is possible.

View 2 Replies View Related

Sort Multiple Columns, With Blanks, And Move All Columns Into One

Feb 15, 2010

I found this code on Ozgrid to sort all columns of a worksheet that were continuous with no gaps or spaces that works well:

Sub CopyToA()
Do While ActiveCell <> ""
Range(ActiveCell, ActiveCell.End(xlDown)).Cut Destination:=Range("a65535").End(xlUp).Offset(1, 0)
ActiveCell.Offset(0, 1).Select
Loop
End Sub

However, I've tried to manipulate the code myself to 1) find all columns that aren't empty then 2) sort each column individually (WITHOUT expanding the sort to other columns) and 3) combining all the numbers into one seperate column. There are many posts concerning sorting but not one that addressed this particular situation.

View 2 Replies View Related

Getting 15 Columns In Spreadsheet To Sort On One Columns As It Alphabetizes

Jun 10, 2014

Have a spreadsheet with 15 columns. In one of the columns is the name of the company and that column is not in alphabetical order. The city, state, zip code, business type and all the other pertinent data about that company is in the same row as the name of the company. My intent would be to put the company names in alphabetical order and keep all the company information in the same row as the company name.

View 1 Replies View Related

How Do You Sort Columns So That They Match Up With Other Columns With Like Data

Jul 2, 2008

I have two columns one is web addresses and the other is email addresses but the rows do not line up. I was hoping that since the second half of the email address matches the web address I could somehow sort them so that the email address column and web address column match up. Here is an example but keep in mind that this list is about 9k long and this is just a sampling so you may not see any in this example that match. Also I may have more than one email address per website.

View 9 Replies View Related

Print 2 Selected Area (highlighted Area)?

Apr 11, 2013

How can I print 2 selected area (highlighted area). I know there is a trick to do that. Right now I can only select (highlight) one area to print, but would like to know how to print 2 or more areas at same time

View 7 Replies View Related

Hide Last 4 Active Columns In Excel Sheet

Sep 17, 2012

I would like to hide/Unhide last active four columns by clicking a command button.

Example:

Columns a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z

When ever i click on command button for the first time, last four columns should be hidden, (w,x,y,z)

On next click, columns (s,t,u,v) should be hidden.

I want similar functionality to unhide the columns with different command button.

View 5 Replies View Related

(Match & Sort) Sort Columns B And C So That A And B Match Up Numerically

Feb 26, 2009

I would like to sort columns B and C (keeping the two lined up together) so that A and B match up numerically.

View 3 Replies View Related

Programmable Print Area: Macro To Set The Print Area According To The Amount Of Data In A Particular Range Of Cells

Feb 25, 2009

Using Excel 2003 I am trying to write a macro to set the print area according to the amount of data in a particular range of cells. I find I can include this instruction

View 2 Replies View Related

Graph The Following Data In An Area Chart With Line Charts Superimposed On The Area Chart

Nov 1, 2006

I am trying to graph the following data in an area chart with line charts superimposed on the area chart. I have a lot of data (and a lot going on) so I'm trying to figure out the best way to show this in excel from a functional standpoing (i can't get this to work in excel!!) to also an asthetic standpoint (dont want it to look terrible or illegible). This is what I'm trying to chart:

1) Weather data (temperature) by region:
So one region, would be: Northwest
I would like the "area" (so a shaded region) to be the min/max of the temperature data for each month.

2) I would like to show the temperature for each year as a line graph on the chart - so you can see if a year falls in or out of the shaded region.

3) I would like to show a company's sales increases across the same months per year as separate line charts. I may choose to just show the biggest outlier year in the end... or to show 2006 (the latest data).

What I am trying to convery with the chart is that the company's sales is or is not tied to weather deviations. I have attached an excel file with the data. I haven't been able to use the area chart or get a two axis chart to work or get it to look even remotely professional.

View 3 Replies View Related

Auto-Merge Dates In 3 Columns And Then Auto-sort Merged Columns

Jan 10, 2012

I have dates values in 3 columns.

A1 - Header - "Holidays in XXXX'
B1 - Header - "Holidays in YYYY'
C1 - Header - "Leaves by YOU"

A2:B11 have static dates consisting of 10 dates in each column.
C2:C11 - the user may enter any date at any point of time.

I would like to auto-merge the dates in all the 3 columns (A2:C11) in a single column say D2:D31 and then the system should auto-sort the column based on dates in any one order. So as soon as the user enters a value in say cell C2, all the 10+10+1 dates should get sorted.

Also the constraint here is a user may not apply all 10 leaves at in a year. So many of the cells may have blank values.

View 1 Replies View Related

Sort All Columns Except For Few

May 3, 2009

I am using a formula but when it sorts with the data in the adjacent cells it does not update the reference cells properly.

For example here is the =SUMPRODUCT(($F$12:$WWX$12>=C1)*1,($F$12:$WWX$12<=E1)*1,ABS($F$13:$ZZ$13))

This code is located on row 13, when I do a sort function with all the data, this code is then moved to row 30, that is fine but the code changes in the following way:

=SUMPRODUCT(($F$12:$WWX$12>=C18)*1,($F$12:$WWX$12<=E18)*1,ABS($F$13:$ZZ$13))

C1 & E1 should not have changed at all, F13 & ZZ13 should have changed to F30 & ZZ30.

I am not sure if there is a way to fix the formula so it updates correctly, or these cells can stay in the same place as long as everything else sorts.

Is is possible to sort all columns except a few?

View 6 Replies View Related

Sort Columns A B C D And E

Jul 8, 2009

I am trying to get columns A:E on Sheet1 converted into columns A:H on Sheet2. I attached the workbook with the macro so you can see what I am talking about. I posted the same macro in the workbook below. It comes close to what I am trying to do but it only sorts based on Column E. I would like to include Columns A, B, C, and D in the sort instead of just Column E so the display will look like Sheet2.

View 2 Replies View Related

Sort Columns - First A / Then B / Then C

Oct 29, 2012

I'm having a bit of trouble creating a macro that will sort columns in order - I've created a form that allows people to select a broad category ie) Schools, then a sub category ie) Primary or Secondary, and type in a third ie) Projects or Teachers

I'm trying to write a macro so that once they enter the form, their choices will be automatically sorted alphabetically, first by column A, then B, then C - to look like:

Schools - Primary - Projects - A
Schools - Primary - Projects - B
Schools - Primary - Projects - C
Schools - Primary - Teachers - B
Schools - Secondary - Projects - C
Schools - Secondary - Teachers - A
Schools - Secondary - Teachers - B
Schools - Secondary - Teachers - C

However I'm having the issue where if column B or C are left blank - the columns are not sorted properly, and the categories in column A get separated out..?

My code at the moment looks like:

Range("A1").Select
ActiveSheet.Unprotect
Range("A1").Sort Key1:=Range("A2:A3"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

[Code] .......

View 1 Replies View Related







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