Sorting With The Use Of CurrentRegion
Apr 24, 2006
'macro sortSales
'You will want to use the CurrentRegion property and look at
'all references to ranges. Any method that does not use an
'If statement, or anything similar, and doesn't change the
'worksheets, except by sorting, is acceptable. Be sure it would
'work on worksheets similar to these but not identical
'As you make changes to the macro, test that it still works for
'Test-Sort 1before you test it for Test-Sort 2
Sub Macro5()
Range("A1:G39").Sort Key1:=Range("D2"), Order1:=xlAscending, _
Key2:=Range("B2"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, _
DataOption3:=xlSortNormal
End Sub
This wasn't in the lecture and I have no idea where to begin correcting it... The worst part is that I have finals all next week and I need to study for them. I usually was able to find my answers on this forum but I don't seem to be able to do that in this case... any help GREATLY appreciated!!!!
I've included the above macro in the attached file.
View 9 Replies
ADVERTISEMENT
Jun 10, 2006
I've been using a piece of code for a while now, but don't really understand how it works. I was hoping someone could help me disect it so I could fully understand how it works.
Sub HLight_UAJ()
Set rFormula = Sheet3. Range("S9:S" & Sheet3.Cells(Rows.Count, 2).End(xlUp).Row)
rFormula = "=IF(RIGHT(B9,4)=""0000"",TRUE,FALSE)"
Set rFilter = Sheet3.Range("A8:S8")
With rFilter
. AutoFilter
.AutoFilter Field:=19, Criteria1:="True"
.Offset(1, 0).Resize(. CurrentRegion.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible). _
EntireRow.Interior.ColorIndex = 7
.AutoFilter
End With
End Sub
I understand everything except this part
.Offset(1, 0).Resize(.CurrentRegion.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible). _
EntireRow.Interior.ColorIndex = 7
I know it highlights the entire row of the visible part of the autofilter, but I don't understand how it chooses what to highlight. I need to know how this works so I can adjust it. I don't want to highlight the ENTIRE ROW, just from Column A to R.
View 9 Replies
View Related
Nov 22, 2006
Assuming I have a data martix which is X* Y. I want to store a range that is at Row Z. I have named a single cell at Row Z as "dataStart". How can I store that Row without the header?
View 5 Replies
View Related
Oct 28, 2006
Probably a really simple solution to this one, I'm trying to create a macro that will jump to a location, select the current region and then create a range name for that region. However, the number of rows in the region will change each time the macro is run as the region is a result of an advanced filter from a huge database. This is what I have managed so far but it to no avail
Selection.CurrentRegion.Select
ActiveWorkbook.ActiveSheet.Names.Add Name:="mergea", RefersTo:=Selection.Address
Ultimately the data selected will become a list for a Word mail merge
View 5 Replies
View Related
Mar 1, 2013
I have a spreadsheet in which the amount of data is not predictable. There are always a different number of rows and columns in this spreadsheet. I need to be able to grab the data that is in it starting with B1 and going to the last row and column with data and put it into a scatter plot. The code that is currently in my macro is as follows:
ActiveSheet.Range("B1").CurrentRegion.Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Range("ForChart!$B$1:$J$1000")
Note: ForChart is the name of the sheet where the data is housed; I have other sheets in this macro.
I know that the last line is the problem. While I figured out how to select only the data I need, when it goes to put it into the chart, it's still using an absolute reference and I don't want that. There are sometimes more columns than J and fewer rows than 1000. I want that data range to be whatever CurrentRegion selected. Is that even something that Excel can do?
View 3 Replies
View Related
Sep 16, 2008
I'm trying identify the active cell row number in a current region for viRows below. Currently I'm getting the active cell row number on the total worksheet.
With ActiveCell.CurrentRegion
viCols = .Columns.Count
viRows = .Range(ActiveCell.Row).Count
End With
View 2 Replies
View Related
Mar 6, 2009
how to create the database and the resulting Pivot Table. My Sales Database includes the Sales $ and GP$ for each line. In the Pivot Table I've managed to create a calculated field for GP %, no problem.
i have also managed to figure out how to express each Row as a % of the Pivot Total, also very easy.
What I need to know is how can I sort my Pivot Table High to Low using these calculated fields?
View 9 Replies
View Related
Oct 4, 2003
I don't understand all of it, but I observed that it works only with mp3 with tag id v1, not v2, anyway I also see another code for tag id v2 given by NateO.
But before entering in detail of what I want, is it possible to edit tag id v1 or even v2, with VBA? Because I want to complete my mp3 informations that are missing, or modify the wrong one, in excel, and then with a macro update my mp3 files...
I've found this code on the forum to get my mp3 informations :
Option Explicit
Public Type mp3Info
Header As String * 3
Title As String * 30
Artist As String * 30
Album As String * 30
Year As String * 4
Comment As String * 30
Genre As Byte
End Type
Sub Getmp3Info()
Dim mp3ID As mp3Info
Dim lngRow As Long, lngFile As Long
Dim lngFileCnt As Long
With Application.FileSearch
.NewSearch
.Filename = "*.MP3"
.LookIn = "H:Daniel" 'Change this to the folder you wish to search
.SearchSubFolders = True........................
View 9 Replies
View Related
Jun 21, 2007
I have a sheet with fixed number of rows but variable number of columns. I have to sort this sheet on the basis of column A starting A2. i am using the following code
Range("A1", Range("A1").End(xlToRight).End(xlDown)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes
But it does not sort properly. To illustrate, i have 10 rows, some 4 columns wide and some 5 columns wide. Since the first row is 5 columns wide , all the rows with 5 columns populated get sorted but the rest get left out.
View 2 Replies
View Related
Feb 18, 2007
This is an example of what is being worked on:
(There are 500+ more lines like the ones below, in groups of approximately 17 lines per page of information.)
* C.B.A. Bank - 2nd reference to branch near Mike's store. (21/03/1956)
* Berwick exams results. (06/06/1877)
* Berwick exams (09/05/1877)
* Steps taken for erection of Berwick. (11/07/1874)
* Berwick tenders for work at home. (11/07/1877)
* W. was a storekeeper at Berwick. (25/07/1881)
* Berwick Brass. (27/06/1877)
* Commercial erected for son. (27/06/1878)
I need to do the folowing to it:
(not necessarily in the following order)
- Insert into Excel
- Remove brackets
- Sort chronologically
- Copy back into program I am using again, in same chronological order.
The desired outcome is to be able to throw into Excel all the lines and press a button, (possibly the Sort Ascending one) and have a chronological list of all entries.
View 10 Replies
View Related
May 11, 2007
I have a table which holds scores as well as other data for players. Is it possible to automatically sort a table based on their points score to see who is top of the league?
View 9 Replies
View Related
Nov 6, 2007
Is there a code or some way for me to sort 4 columns together? I want to sort a list of employees and for each employee theres a column with their Lost Business, customer satisfaction, and two more columns. I want to sort all the columns at the same time so that the best employees overall will go to the top of the list and the worst ones will be at the bottom. Whenever I use the sorting feature it does each column independant of the others so everytime I sort a new column it just moves around the last one I sorted.
View 14 Replies
View Related
Jan 12, 2008
I have five columns of data A to E. In each row there can be data in either 2 3,4 or 5 of the columns. What I want to do is essentially condense the data. So in columns, say, G,H,I J,K if there is data in column A then It goes into column g, if not it asks if there is any data in column b and so on. Then for H it does the same but one on. I have attached an example to show what I would like to happen.
View 9 Replies
View Related
Jun 20, 2008
I created a data base that had a hyperlink to a plan of the store so we can find parts easy. With a lot of help for people here on this web site, it works very well.
My only problem now is that I sorted my Database by the hyperlink column. It made it easy to do one hyperlink and pull the fill handle down for the rest of the same hyperlink reference.
When I went to resort the database so that the parts were in alphabetical order. The sort worked well except that the hyperlink for lets say cell F87 was not sorted. Example : - F87 hyper link presort had the reference to Rack 10 self B. Post sort it had the reference to Rack 16 shelf C. But when clicked on it still took you to Rack 10 self B.
View 11 Replies
View Related
Nov 14, 2008
I'm trying to sort this spread sheet so that each email address will have its own column assigned to it. Ie jaz@hello.com will alway be column C and harry@hello.com will always be in D. The problem is I've around 1200 rows of infor and the email addresses are all over the place..
View 5 Replies
View Related
Dec 20, 2008
how can data sorted be more frequency as most frequent on top and as the following:
1
1
1
2
2
3
3
3
3
to
3
3
3....................
i attach file
View 3 Replies
View Related
Aug 9, 2009
I have around 1900 dates that I need to sort into order but I need the data to be displayed as DD/MM in chronological order eg 01/01, 02/01 etc.
At the moment the dates are DD/MM/YYYY in one column. Have tried sorting them into date order but have found that they're sorted by YYYY. Is there a way of sorting them DD/MM instead?????
View 5 Replies
View Related
Aug 26, 2009
I should sort numbers in ascending order from left to right, but the numbers sort only from right to left.
This is what I want:
1.3.2.2
1.4.1
2.1.1.1.1
3.2.3.12
3.3.1
And this is how it goes:
2.1.1.1.1
3.3.1
1.4.1
1.3.2.2
3.2.3.12
There are also numbers that have letter at the end (1.2.3a), and the cell format is text.
View 10 Replies
View Related
Sep 8, 2009
I have an array of data that I need sorted. I did a Custom Sort. However, the data did not all sort properly (some of it did). Any ideas as to why this happened and how I can get it to sort correctly?
View 5 Replies
View Related
Oct 6, 2009
Im not entirely sure about this but it seems like if I have data in columns A - Z and sort in one of them, the data in Columns AA - AZ does not move accordingly rather it stays fixed.
View 4 Replies
View Related
Oct 7, 2009
I have a column showing date DD/MM/YYYY. I want to be able to get a new column stripping out the days so that I can chart changes over a month rather than each day of the last 2 years. I have tried altering the cell format to MM-YYY although this shows the format I want when i run a pivot chart it will still create records for individual days. I then tried copying and pasting the above as text only. This results in the pivot chart sorting A-Z rather than by date.
View 2 Replies
View Related
Nov 23, 2009
I have information in columns A (First name),B (Last name), C (Misc), D (misc). Is there a way that someone can enter in new information at the bottom of the list and it will automatically sort by the last name once saved or closed ect.. ? I have people not very familiar with Excel and the keep messing up inserting rows and I don't want them to sort. If they could just enter information at the bottom and save it, then when someone opens it to just view it, it will be in order.
View 6 Replies
View Related
Jan 31, 2010
The problem is sorting alphabetic.
View 12 Replies
View Related
Sep 18, 2007
I am mapping this file on the basis of a lot of fields. But the end product has 15 currencies. My requirement is to separate the accounts on the basis of the currencies and then consolidate it on the basis of GL account. For example, first I have to find all the accounts that are in USD and then if there are 2-3 similar GL accounts, then I will consolidate on the basis of the GL accounts. Please find attached file.
View 9 Replies
View Related
Jun 15, 2008
in the the attached image of excel
i want to see what is the bigger number in the row 49
then sorting these numbers descending and write it in C52
then make a quick equation on in each number ,and this equation is to deduct this figure from each other nine numbers and see what is the zero or above zero result and put it the cell next to this number (column E) starting from E52
example
527.7 is the maximum number so i put it in the c52
and then deduct 527.7from each number ,like the following
41.0-527.7 = negative value (doesnot count)
21.8-527.7= negative value (doesnot count)
....
.... so on
and we will see that the only greater than zero or equal to zero result is only 1 result so we put the figure 1 in the cell E52
and so on on each number
View 11 Replies
View Related
Feb 25, 2009
I have three columns that are added together. Depending on where I need them to go, I need three options. For example, E6,E7,&E8 are added up, on E9, I need to be able to direct it to "A @ E10", "B @ E11", or "C @ E12"
View 3 Replies
View Related
Mar 14, 2009
I am encountering numerous problems compiling a macro to sort some pretty messy data. Hoping some of you could take a look and offer some suggestions....
The Data I need sorting is shown in the 'DATARAW' worksheet, this comes in every day and shows positions for each traded period. Usually 48 periods which I have highlighted although the data download could somtimes consist of 46 or 50 periods.
Sheet 1 (DATA RAW) contains data which I download daily.
Sheet 2 (Data Sort) is how I need it to look after sorted (I only need the data in DATARAW replacing, not another worksheet creating).
So for example:
Where column A = SP8 and the adjacent column B = 1 I would need:
All values which = ECN to concatenate the value in B to the reference in column B directly adjacent to the value next to it, until the folowing rows are presented:
So for instance the first value reported would be 131964, second 131979 etc... until the following text is shown in the rows below
IDD
CAT
ACE
ACT
ACE
ACT
When these appear I need the entire rows to be deleted.
Then basically the same for the next period where SP8 appears in column A and 2 appears in Column B.
also, the final result should contain no formulas so it can be referenced by lookups etc... also, all numeric colums should be stored as number so excel doesn't get confused.
This will basically repeat itself until the end of the file showing the same split as aove but with ZZZ as a footer. Which I also need to delete. :-S
IDD
CAT
ACE
ACT
ACE
ACT
ZZZ
Rows containing the values below also need deleting.
AAA
NOH
DSI
AED
I've used colours in the attachment but the actual file doesn't have any.
View 11 Replies
View Related
Jun 30, 2009
I use my VBA code to sort the data in certain columns. I have been doing it with the following
View 4 Replies
View Related
Jul 3, 2009
I am doing inventory right now and need help sorting numbers that I have. Ex:
I have numbers like
10
1001
101
AB1394-10
AB2948-MPG
XD89-MPG KIT
F64 KIT
GSW294-HD10
I need something that will help me sort it by the numbers only (numerically by the first part only). I want the above numbers to look like the following after sorting:
10
F64 KIT
XD89-MPG KIT
101
GSW294-HD10
1001
AB1394-10
AB2948-MPG
Basically, I'm trying to get the computer to sort the numbers numerically by disregarding any letter before the number.
View 7 Replies
View Related
Nov 27, 2009
I have columns with sort filters, columns with tables, unformatted cells and frozen panes. When sorting one column how do I make all other columns sort simultaneously with it?
View 12 Replies
View Related
