Sort Changing Range
Aug 24, 2007
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.
View 9 Replies
ADVERTISEMENT
Nov 3, 2011
Currently I have a macro set up that sorts a range.
It sorts numbers first in ascending order and then text comes at the bottom of the sorted range.
Is there any way of reversing this so that text appears first and then numeric values in ascending order below?
View 1 Replies
View Related
Nov 26, 2008
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 Related
Jan 6, 2010
I'm trying to select a range that will be changing by column. I'm not sure why my syntax isn't working. What I've got:
View 2 Replies
View Related
Sep 8, 2006
I am trying to create a graph for a range of data that updates monthly (adding an extra month each time). I wanted the graph source data to update automatically each time the data is refreshed so used an OFFSET formula to identify a named range. I then point the graph to the named range as the source data.
When I enter the range as the source data the graph picks it up. However, when I re-enter the source data option on the graph it has converted the named range into a cell written range (ie. replaces "=QUALITY" with "='Front page'!$B$7:$J$10" - which therefore will not update when the range increases.
View 4 Replies
View Related
Mar 25, 2009
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
View 9 Replies
View Related
Apr 3, 2008
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).....
View 9 Replies
View Related
Oct 20, 2008
I have been tasked with creating a scheduling system for my work. There are three groups of shifts that can be selected, Opens, Swings, and Closes. I have been asked to make the cells turn red if a user selects a sequence of shifts. We are trying to avoid having a person working three scenarios:
Close>Swing>Open
Close>Off>Open
Close>Open
If they select the shift that meet this scenario, then I need the cells to turn red. The complete list of shifts are on the "Data" tab and the the three scenarios have been inputted into the "Jan" tab in E13:G15.
I am using the code below to change the background of the cells in my worksheet based on what is in the cell. How can I modify it to accomplish the changes below and meet the criteria above?
View 12 Replies
View Related
Sep 12, 2006
I was thinking of placing my total line above my data instead of at the bottom. How would the formula look to sum each column?
For instance, = sum(A6: to what?) How do you specify what the last row is without having to do something like = sum(a6:a65536)?
View 9 Replies
View Related
Apr 22, 2014
I have a master workbook ("database") containing many named ranges (ex "Contact"). I have another workbook template ("estimate") that I rename for each project and it too has named ranges (ex "Contact") referring to the named ranges in "database" (ex =DataBase.xlsm!Contact). I am saving "database" on multiple PC's and syncing to the cloud (skydrive / onedrive) so that I can access "database" from multiple PC's. What I'm finding is everything works fine as long as "estimate" or whatever I've renamed it is opened on the same PC it was created on.
If I open estimate on another PC the named ranges in estimate are renamed by addition of a prefix including the complete path to "database" (ex "='C:UsersJasonSkyDriveDocumentsDaikinServiceToolsDataBase.xlsm'!Component"). This renaming prevents the named ranges in "estimate" from working on other pc's because this specific path doesn't exist on other PC's, only the PC it was created on. Is there a way to name the named ranges in estimate by only the workbook name (=DataBase.xlsm!Contact) without the entire path being automatically added as a prefix?
View 5 Replies
View Related
Apr 14, 2009
I want a conditional format change to happen when certain criteria is met.
I have a conditional formula in column F
Let's say F173
If the value in A173 value is found anywhere above cell A173, then make a blue box surrounding cell F173.
I want to be able to copy this conditional format down column F
View 9 Replies
View Related
Apr 24, 2006
I want to have the "category (x) axis label" to change with each new entry into column A. I would like to start from the last entry and go back 30 and have that change each time there is a new entry. I guess what I am trying to say is I only want to chart the last 30 entries.
View 2 Replies
View Related
Aug 8, 2006
I'm trying to count the word "Standard" in a series of ranges. Say A10-A15, then maybe A40-A43...it keeps changing from 3 rows upto 10 rows.
Is there anyway to count in a range when the range keeps changing in the amount of rows. Like it won't ever be only 5 rows, it may be 3 rows, 6 rows, 9 rows and then 2 rows.
View 9 Replies
View Related
Mar 6, 2009
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
View 3 Replies
View Related
Jan 18, 2007
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.
View 9 Replies
View Related
Feb 25, 2014
I am doing an Advanced Filter, and I am using a named range in my VBA code that decides the Criteria range.
The dynamic named range is called 'Criteria' and this is the code:
VB:
=OFFSET(Sheet1!$A$2,0,0,Sheet1!$BZ$23,41)
However, when I run the VBA code, although it works perfectly, afterwards the named range has changed to:
VB:
=Sheet1!$A$2:$AO$3
I just want this to remain unchanged, as it works great. This is my VBA code:
VB:
'Filter and send data to new sheet
Sheets("Sheet2").Select
Cells.Select
[Code]....
View 4 Replies
View Related
Jul 14, 2014
I have a workbook, with 2 sheets. first page has graphs, second has data. in my charts I refer to a named range on the second one as follows: Data!Named_Range, where "Data" is the name of the second sheet. However when i save this, it changes the range of the chart to: '101044.xlsm'!Named_Range, where 101044 is the name of the workbook. This is a problem for me as this workbook will often be loaded into a document handling system where the file will change name, throwing out all sorts of errors because the chart path is invalid, and i have to change the range of all the charts manually. why does it have to use the file name when reffering inside the same workbook? and how do i force this to not change?
View 5 Replies
View Related
Mar 6, 2014
I have a workbook that has a saved range. I created it with the Name Manager. See below:
[Code]....
When it looks like that the VBA works great and creates my pivots. But after I run my first macro to mess format my source data the range ends up looking like below:
[Code] .......
It is for some reason changing the "A" to "Y".
How I can get it to stay with the original data?
View 2 Replies
View Related
Jan 17, 2009
I have some code that uses offset to select a column of numbers
View 2 Replies
View Related
May 11, 2009
I am trying to find the max value within a range (14 cells) contained in one column ("C" in this case). This range will change corresponding to the current cell. Ex. as the current cell moves down (or increases in number) the range of cells to find the Max in moves down (or increases in number) as well. The Max value will then be used as a variable in a equation but for now just getting it into a cell is fine. This seems like it would be easy but it is driving me absolutely batty!
I currently have the non-working code located in a For loop that contains other calculations as well. I've taken those calculations out since they work fine but cloud the bad part. So far I have tried:
View 3 Replies
View Related
Jul 26, 2006
I have a spreadsheet that has multiples ranges. Some values are displayed as negative values. I would like to create a macro that allows me to first select the range/s, (maybe using a input box) I want to change and then display the negative values as positive values.
Would I need to create a separate macro to do the opposite (change the values that are positive to a negative value)?
The other macro I’m trying to create is a similar type. What I would like this macro to do is first select the range I want to change and then divide all the cells in the range by 100000
View 3 Replies
View Related
Sep 4, 2006
I am trying to automate a graph for data that is not always the same amount but always in the same format. I have attached a spreadsheet that has an example of the data.
Columns B, D, & E are the ones that I am interested in charting in a line with markers type graph, but the rows may be 100 for one set of data and 1000 rows for another set of data. I am trying to get the macro to open a new worksheet and as part of the macro create a userform that will ask for the text information on the top of the worksheet (as this is not always correct when imported).
View 5 Replies
View Related
Oct 5, 2006
i have a chart on a worksheet, it contains multiple series (3) in which i wish to change the values/ range of each series of the chart through vba. Thus far i have struggled due to the lack of intellisense, in which i tried
Worksheets(1).ChartObjects(2).Chart.Series("Fails").Values = Range("b12:b14")
with "Fails" being the name of that particular series.
View 3 Replies
View Related
Oct 24, 2006
I have a chart updating on a weekly basis
a) can i select a whole range without the chart picking up any blank cells or zeros, if so then a lot of my other question will be redundant
b) I only want to show 4 weeks at a time, so when a new column of data is added on, how do i get VBA to shift the range accross 1 to the right e.g.
week 36 37 38 39
Shift to
week 37 38 39 40
View 5 Replies
View Related
Oct 25, 2006
I’m wondering if it possible to have a variable in a Formula. I was looking around, but non of the other examples I found was working. The code I was trying is:
Sub All()
Dim lngDataRows As Long
Dim lngDataRowsSum As Long
' add sum
lngDataRowsSum = Range("A5").End(xlDown).Row
Range("A" & lngDataRowsSum).Offset(1, 0).FormulaR1C1 = "Sum"
Range("M" & lngDataRowsSum).Offset(1, 0).FormulaR1C1 = "=sum(M6:M & lngdatarowssum)"
End Sub
Another code I tried was from this thread
Sub All()
Dim lngRowsBottom As Long
Dim lngRowsTop As Long
' add sum
lngDataRowsSum = Range("A5").End(xlDown).Row
Range("C10").formula = "=sum(R["&lngrowstop&"]C:R["&lngrowsbottom&"]C)"
End Sub
The error message is: “Compile error: Expected: enf of statement” and the part “[C:R[“ is highlighted. Tried various ways to solve it but nothing was working.
Does anyone of you know how to get it up and running?
View 3 Replies
View Related
Sep 25, 2007
I have created a macro (data on the left hand side) that allows me to copy data and paste it into an sql file. The problem i am having is that the data varies (e.g. data on the right) so this doesnt actually work. i was thinking of naming the values within the name ranges and then writing code to see if i can copy particular named range values but i dont know if this will work.
View 9 Replies
View Related
Feb 18, 2008
I am trying to figure out how to copy a variable range of data from one worksheet to a new one. I have the worksheet part of it figured, but not the range part. How do I define a range to include both columns and rows in VBA?
View 4 Replies
View Related
Apr 29, 2008
i have 118 cells and they are changing every 10 second so, how to gather the max value from these cells and displaly them in Marquee box or in pop message?
View 3 Replies
View Related
Jun 27, 2014
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 Related
Oct 20, 2009
I 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.
View 4 Replies
View Related
