I am trying to write a macro to sort a range of data by another column. Here's the code that I already have :-
Code:
Public Sub sort_data(sort_range As Range, key_range As Range)
' MsgBox sort_range.Address
' MsgBox key_range.Address
sort_range.Sort Key1:=key_range, Order1:=xlAscending, Header:=xlYes
End Sub
However I get a run time error 1004 stating that the sort reference is not valid.
I have a Ranking tab that has the persons name, weeks, months that subtotal into 2 columns, MTD and YTD. I have set up with 2 Macros to sort based on two columns based on their selection of MTD or YTD.
What I did not take into consideration is if a person was added at the bottom of the list.
Is there a way to modify this to include rows that may be added?
VB:
[SIZE=4]CODE HERE[/SIZE]
VB:
[B]Macro1
[/B]Sub SortYTD()
[Code].....
I have a named range that is being updated via a macro. After the update, I want the list to be sorted. The named range is "Projects". This name is not a variable...it is a named range within Excel itself.
Any code to sort this at the end of my existing macro.
I recorded this macro - which was a simple copy and paste and then sort the results, however it works in excel 2007 and not in 2003. Even tried to record the same in excel 2003 and it still does not work. It seems to fail at the sorting stage
Sub sortprices()
Sheets("Rates").Select
Range("B229:C241").Select
Selection.Copy
Sheets("Results").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Results").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Results").Sort.SortFields.Add Key:=Range("D4:D16") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Results").Sort
.SetRange Range("C4:D16")
. Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("C4").Select
End Sub
I have an Excel sheet that lists all of my dvd collection.
Its very easy to use, all I need to do is type the movies I have then click a sort button.
The everything goes into alphabetical order.
The problem I'm having is it only goes to 999 I have tried to extend it to 3000.
I can't seem to find the formula to allow me to extend the perimeter past 999.
I am copying and pasting from two different " timesheet" spreadsheets into a list. One of the timesheets has blank rows. I am attempting to sort the blank rows to the bottom after I paste the data, but every time I do, it either replaces the top row with "true" or deletes the headers,
Sub SortBlankRows()
Dim rngCurrent As Range
Dim c As Range
Dim inUsedRow As Integer
Set rngCurrent = Workbooks("Payroll Summary.xls").Worksheets(1).Range("A1:J1")
inUsedRow = Workbooks("Payroll Summary.xls").Worksheets(1).Range("D65536").End(xlUp).Row
rngCurrent = rngCurrent.Resize(inUsedRow)
rngCurrent.Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Key2:=Range("F1") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End Sub
I have recorded a macro to sort a range of cell based on two values that is dependent on time in another cell. I now want the macro to run automatically when refreshing the workbook with F9, so as the time changes so will the sorting. Everything works fine except the sorting doesn't refresh when F9 is refreshed.
My recorded macro is:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, FillCT As Long
For X = 1 To 4
If Cells(Rows.count, 1).End(xlUp).Offset(0, X - 1) <> "" Then FillCT = FillCT + 1
[Code] .....
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 3 Replies View RelatedI am looking for a macro to sort my colums in ascending order the range i need sorted is from A7:F107, it will be sorted with the numbers in the A column ie 1 - 107. sometimes different numbers get added and it has to be sorted in number sequence.
If possible i would also like a macro to protect this range after the sort,
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my inner-most loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003.
My
Public Function SortRange(rngToSort As Range, valCol As Integer)
Dim Swapper As Variant
Dim i As Integer, _
j As Integer, _
k As Integer
For i = 1 To rngToSort.Rows.Count
For j = 1 To rngToSort.Rows.Count - i
If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then
For k = 1 To rngToSort.Columns.Count
Swapper = rngToSort(j, k)
rngToSort(j, k) = rngToSort(j + 1, k)
rngToSort(j + 1, k) = Swapper
Next k
End If
Next j
Next i
SortRange = rngToSort
End Function
Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:
=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)
When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
Windows("estimate sheet one.xls").Activate
I would like it to sort for me and I can't seem to get that going. The sort order should be by C ascending, then by B ascending and by d ascending. I am adding this to a macro that is already running. The beginning of the macro copies the sheet and renames it based on myinput.
HTML Dim LstRow As Long
Dim Rng As Range
LstRow = Cells(Rows.Count, "A").End(xlUp).Row - 1
Set Rng = Range("A2:m" & LstRow)
Rng.Select
i have a large sheet that was set up starting at cell A3
A3:P19 is one week of data
A20:P38 is the next week of data
Each week contains 17 rows.
What i am trying to get is something that will Name each week, starting with the first week of data A3:P19 "1"
the next 17 rows, a to p is named 2
the next 17 rows, named 3
etc etc.
Then i want to sort by range, in decending order. ( I hope this is possible)
is this doable? it will save alot of time if it is.
I'm highlighting a range and then sorting column F from Largest to Smallest. The macro doesn't sort it at all. Added my code tags.
View 1 Replies View RelatedI am trying sort a bunch of different ranges. So I am trying to use the same sort code but run an array of ranges through. This code is for only two ranges
CA3:CD200 and CF3:CI200. I want to pass the ranges as variables through the sort code but I cannot get it to work.
I have the following code for a button on a form which is supposed to add the text entered into textbox1 of the form to the bottom of a range of cells and then sort that range of cells in 'A-Z'.
View 5 Replies View RelatedI have a spreadsheet that I am tryng to sort. I am sorting a range of cells. I get my list sorted how I want but then I want to go one step further which is why I am seeking assistance. After my initial sort I want to select just a few specific cells and resort only them.
I am identifying the cells I want to sort by placing "zzzSort" in column V (V267 to V516) ("zzzSort" is placed in the desired cells automatically).
Is there a VBA code that I can place after my original sort code that would select the rows with "zzzSort" in Column V and then run only those ones through anouther sort.
I know that I could just click sort, decending, "points" etc but I figure there must be a way of it auto updating.....
Please see the attached file - I have attempted doing it with Ranking Formula but forgot that it would just give me error messages when two have the same points so I got rid of that.
Have tried doing it with other formula found on this website and others, however I appear to be stuck. I might be doing it wrong but as I'm not manually putting in the "points", they are being worked out with =( SUMPRODUCT((H4:H16={"W","D","L"})*({3,1,0}))+58) for example, it does not move their position.
What I would like it to do:
1. People select from drop down list whether they think the team will W, D, L
2. The Table to the right should automatically change the points dependant on the result
3. The Teams position should change automatically in line with their new points total.
I am using the following:
Sub Sort_Labour_Dates()
With Range("A10:Z18")
.Sort Key1:=Range("A10:A18"), Order1:=xlAscending, Header:=xlNo
End With
End Sub
This works perfect but I will be inserting rows between A10 and A18. So when it comes to sorting out the range with more rows it will not sort all the data.
But I do not want to be sorting the whole column as I have seperate data below hence why I will be inserting rows between A10 and A18.
I have it a workbook with 7 worksheets. 6 out of the seven have unique data sets and the seventh one totals them all up. Now I have sorting macros in each of the 6 unique worksheets but unless they are returned to the original sort order (ie: by persons last name: before changing to the next worksheet the totaling will be incorrect as the data is not sorted accross the entire workbook. The reason I sort while inside the worksheet is plainly for printing purposes, then I want to reset the sorting to Last Name column. Anyway I tried to add a macro to sort the data on worksheet_deactivate but instead of sorting the data on the sheet that the user is leaving it does the sorting on the sheet they click to goto. Any ideas? This is similar to another post Sheet Event Code Not Firing:
I've tried putting this code in each sheet individually and the ThisWorkbook / Workbook section as a Private Sub with no avail and the macro I'm trying to run is located in Module1 and is named "data_b"
Excelforum.jpg
What I want to do is in column 5 row 1, I want it to show a value only if all 4 columns, row 1 are filled in. For example in the picture I want to show a blank in the first 8 rows but to start showing a value on the rest of the rows, in this example IPE 240 to be the first value. When it shows this value from this row I want it to obtain the value with the highest percentege inside. For example in this picture the correct value first value to be shown in column 5 will be IPE (84%)
Ive done this code so far: =IF(AND(B256<>"",D256<>"",F256<>"",H256<>""),Here I want it to show the value on the row that has the highest percentege,"")
I have a spreadsheet we use as a "Stock Location guide" and the critical colums are as follows, Column A = Location, Column B = Date and Column F = Material Description. The following code is what we have been using to sort the guide in order of F, B and then A :
[Code] ..........
What we want to do is swap "A" and "B" so "A" is sorted before "B" and also to change the way"A" is sorted. Certain cells in "A" are yellow and I want to be able to sort by Cell color with yellow on top.
I am running a formatting excel, and I will also need to select from cell P30 till last row, then sort alphabetically by moving the other rows data too.
View 3 Replies View RelatedI have a recorded macro to sort Data with 3 sort fields but has a Fixed range. The Data will increase each week so I need to modify to account for growth.
View 2 Replies View RelatedHow might the code below be changed to handle any number of rows? Right now it's set (from a recorded macro) to go down through row 237, but I need it to sort anything from just a few to maybe a thousand.
Cells.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("C2:C237"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("B2:B237"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("K2:K237"), _
[code]...
In column B, I have a series of named ranges. The named ranges can vary in order. If I wish to reference a range from a named range say Range("I") to the next unkown named range,
View 9 Replies View RelatedI would like to use a macro to sort multiple worksheets simultaneously. I need to sort on last name (column A) then first name (column B) and my data does not start until the 8th row (A8:AF8). The data range should be the same for all worksheets that I need to sort. I found the code below here on ozgrid (Dynamic Sort Across Multiple Sheets) but I'm not sure if its appropriate or the best way to customize it so that the 1. Can sort on two criteria
2. Is specified to nonblank cells in a specific range, A8:AF8 and below
Sub DynaSort()
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
Select Case wsSheet.CodeName
Case "Sheet1", "Sheet2", "Sheet3", "Sheet4"
With wsSheet
.UsedRange.Sort Key1:=. Range("B14"), Order1:=xlAscending, Header:=xlYes
End With
Case Else
'Nothing
End Select
Next wsSheet
End Sub
I have some trouble to find a solution to sort blocks of data, the blocks have multiple headers and i would like to sort by the user column C.
I have attached a sample to easier understand my ramblings.
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..............