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 have a bunch of nameds ranges that are essentially entire rows. For example, Rows 1 through 5 are named range "Range_A", "Range_B" is Row 10 through 20. I was looking for a way that I could have the value of one range assume the value and size of another range.
For example
Range_A = rows 1 through 5
Range_B = rows 10 through 20
Now, if I use the following macro all that happens is the first five rows of data is copied over to Range_B while Range_B's other 5 row values are not copied over.
Sub Example()
Range("Range_A").Value = Range("Range_B").Value
End Sub
What I want to have happen is have of Range_A expand to the same number of rows the other range has (which is 10 in this case) and assume the value of Range_B. Is there a way to do this? In my workbook I have a number of ranges that need to be replaced with other ranges and I want them to assume the size and values of the range they are supposed to equal.
I have data coming into my sheet from an Access file into columns b:l which automatically get covered by a named range which changes according to the size of the data coming in.
What I'd like to create is a named range which includes this original range as well as columns a and m:p. Is this possible using Offset at all?
On top of this my data from Access comes into row 2 and I'd like the named range to cover row 1 too.
I have a problem setting up an offset range on my charting example ( attached).
My X axis will always be row A
I want my charting range to be more than just the one column and since the months will keep expanding I'm looking fopr the easiest method to do this.
My range could start at 1 row and finish at any other. User Defined.
Bu I want that whole range to be included in the charts. This is a stacked chart.
So in my example I'd liek the shaded bit as a stack chart. 3 bars for 3 months with 4 stacks in each one.
So I have recorded a macro that filters for certain criteria in a master list of projects, then copies the projects that meets this criteria and pastes it into a timeline in another worksheet. However, because the list of projects gets bigger and smaller when the filter criteria changes, right now I have to create twenty extra rows so that the pasted list doesnt ourgrow my timeline. How I could input a few lines in my macro so that the table for the timeline expands as the number of prjects grows or shrinks.
One method I have tried is to copy the filtered data and "Insert Copied Cells" into the table, however I don't even get the option to insert copied cells after I have copied the data...
My chart data range is: ='Summary Data'!$A$2:$BF$8.
Since it expands a few times a week to BG, BH, BI etc., it seemed to make sense to use the standard OFFSET and COUNTA formula to expand it. The every time I typed the Offset formula in, it worked but each time I save it, it changes to an updated (correct) fixed reference. i.e. back to the format above.
Many threads I've read seem to say that you should be able to use an expanding range in charting.
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 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).....
[qoute]I have five cells that I am working with cells A,B,C,D & E. cells B & C are time formated. Cells D & E are number formated and equal the number of hours between cells B & C. the difference is that if cell D is less that 2 hrs then cell E is to equal 2 hrs but if cell D is over 2 hrs then cell E equals the value of cell D. All this works fine but I am needing to add in the value of cell A. So if I have say CT in cell A then cell E will equal the value of cell D no matter if is less than 2 hrs[/quote]
View 5 Replies View Relatedin this example below i have record entrys of tps,rpm,etc etc in time.
typically 4 records every 1 second.
how can i make the data 10 rows for every second?
i.e can we take the column readings after D i.e row2 then at row4 and then insert 8rows between the 2 and enter averages.
1353ecu *ABCDEF1LogIDLogEntryDateLogEntryTimeLogEntrySecondsTPSRPM21########13:47:190.55517.25491406.2532########13:47:190.80416.078431531.2543########13:47:191.03716.470591437.554########13:47:191.27223.529411343.75 Excel tables to the web >> Excel Jeanie HTML 4
the whole sheet is pretty big
1353ecu ABCDEFGHIJKLMNOPQ1LogIDLogEntryDateLogEntryTimeLogEntrySecondsTPSRPMBatteryCoolantTempAirTempTimingAdvO2SensorSpeedInjDutyCycleKnockSumLoad1BOctaneFlagknock_flag21########13:47:190.55517.25491406.2513.63938195.8122210.956483.2854850031.2100032########13:47:190.80416.078431531.2513.71271195.8122200.897923.2854850025.2100043########13:47:191.03716
copying a document that was made in word into Excel. I set the print area and page size but it seems I can keep adding columns. When I check the print preview it still shows the one page but it appears wider.Will it automatically create a new page if the page size limit is reached?
View 2 Replies View RelatedIs it possible to make a self expanding table. What I have is a sheet with a table with loads of biking tracks with track number, date, nr of miles and so on...
I'd like to make a table on the second sheet so that I can see only the tracks that auto fill as I put in info about number of miles or date or track number.
And I'd like it to auto expand so I don't have a lot of wasted space on the page. I'd like the TOTAL amount of miles, when i put in multiple tracks, to move up or down when I add or remove tracks...
This is probably something very simple, but I need to find a way to stop row heights expanding when text in a cell wraps. Basically, I have a spreadsheet of projects, and one column is for 'notes'. When the text in this column starts to wrap, Excel automatically increases the row height to make all the text visible - but I don't want that to happen.
I guess there's a button to press somewhere, but I can't find it!!!
I have a script that converts any List based Data Validation cell into a combo box when you click on it...What I am trying to do now is find a bit of code that will auto expand the combo box.
View 3 Replies View RelatedI 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 have a problem in expanding/collapsing fields in pivot table.
I have source table with GROUP, SUBGROUP, ACCOUNT and AMOUNT. In pivot table I've put GROUP, SUBGROUP and ACCOUNT in Row labels respectivly, and AMOUNT in values. There are same names in SUBGROUP for different names in GROUP. (for example: groups are Production costs and Distribution costs and in each of them there are subgroups Personnel costs and Other costs).
The question is: Is it possible to expand only field Other costs in group Distribution costs while field Other costs in group Production costs stays collapsed?
I have a list (C5:C16) and I want people to be able to add more names to the bottom and it add to a dropdown list on another page.
View 6 Replies View RelatedThe table below has three rows and 7 columns. Row 3 shows cumulative sums of the values in Row 2. Sometimes, Row 2 may not have values (not even a 0). The formula for C3 is =SUM(B2:C2) and =SUM(B2:G2) for G3. The challenge is that I have a very long row and summing it individually would be inefficient.
Is there a way of automating the formula so that the cell ranges expand automatically which I could use to fill to the right?
A BCDEFG
1Row 1123456
2Row 21041503
3Row 3115202023
When not programming I sometimes use very long commands (e.g. multiple conditions with data importing from closed files). The command line in the rule expands such it hides the columns headings making the writing very uncomfortable. is there a way to force the line to be wider without spilling into the data (like another ruler line)?
View 3 Replies View RelatedI have thousands of phone numbers that hundreds of them are look like this:
123456-8 that means : 123456,123457,123458 or this
123456-57 that means : 123456,123457
There are not any standard of numbers after dash (-) in my data.
how can I expand them to new "rows"?
bob 123456-8 ->
bob 123456
bob 123457
bob 123458
So, I have a list of data like this:
Type
Variety
Country of Origin
Price
[Code]....
Obviously I can do this using text to columns and transpose but typically I'm dealing with much bigger sheets where being able to do this automatically is a big time saving.
The column with commas in it can vary in position so I'd like this to work on the cell/column that is selected.
Ideally I'd like it to work it's way down the sheet expanding out every comma separated list in the selected column in this fashion but even something that worked on the current cell would be handy.
I think roughly I need to split the cell into an array using the comma as a seperator, possibly scrub the spaces from the text, copy the row as many times as there are things in this array (using ubound?) and then transpose the array into an area starting at the selected row and going down as far as there are things in the array but a bit lost with actual implementation.
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
[Code]....
Why do my named ranges expand each time I import data from Access? I then have to go my named ranges and reset them. The reason I am using named ranges is because of the indirect functions I am using.
View 9 Replies View Related