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
ADVERTISEMENT
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
View Related
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
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
Jun 12, 2013
my spreadsheet attached, when you open it up you will see the tabs on the bottom, each of those tabs are departments.
This spreadsheet track all the jobs coming through my department, placed by other departments.
On the right hand side of each sheet are print numbers columns, and total hour columns.
at the end of a job I record how many hours the job took or how many dollars it cost. Then the job is close out and that line of information is sent to the archive page.
The archive page is where all the information is stored, print numbers hours and dollar amounts and so forth.
what I would like to do is, set up another sheet within the workbook that would sort all of the part numbers and associate them with hours or dollar cost.
I'd like to have it start the search when a new job is entered and I put the print number into a cell, in any of the department pages.
View 3 Replies
View Related
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
May 13, 2013
I am trying to write a Macro to basically do a lot of redundant formatting for me and I am stuck on one part of this code. Everything else works just dandy, but I am having the darndest time getting this Macro to sort by 2 columns instead of just 1.
I did the basic first step and recorded myself sorting the datasheet by both columns in 1 sort and got the code, but the problem is that the number of rows will change day-to-day, week-to-week, and so on, so I need it to not specify a cell number as the end of the range.
While searching around, I saw a very handy little piece of VBA code that will search for a header name, which is really nice because that would make it much easier to share this Macro with my coworkers who have different reports that all have columns in different locations.
So, I have a multi-part question. I will start with the most important one:
How do I alter this to not specify A9590 and D9590 as the last cells in those ranges?
ActiveWorkbook.Worksheets("Date XREF").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Date XREF").Sort.SortFields.Add Key:=Range( _
"A6:A9590"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Date XREF").Sort.SortFields.Add Key:=Range( _
"D6:D9590"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
[code]....
Is there a way to have the Macro find where the header row is? The header rows in our reports can appear anywhere from Row 1 to Row 8 depending on how the report is set up (usually we have some information about the data, date range, etc at the top).
Dim c As Integer
With ActiveWorkbook.Worksheets("Date XREF").Range("A5").CurrentRegion
c = .Find(What:="Assignment ID", After:=.Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Column
.Sort Key1:=.Cells(1, c), Order1:=xlDescending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With
View 2 Replies
View Related
Jun 25, 2013
I have a sheet that looks like this...
Date
Start #
End #
1/1/11
1000
1099
[Code] ........
And I want to sort it to look like the following, in order to track each number to a date and to enter into an external database...
Date
#
1/1/11
1000
1/1/11
1001
1/1/11
1002
I need to list the entire thing instead of having starting and ending part #s. Any macro to do this? The actual spreadsheet has 250 dates and over 30k part #'s so doing it out is not really an option.
View 9 Replies
View Related
Apr 15, 2008
I have a spreadsheet with 28 columns. I need some column headings to be amended when I run a macro and I also need some columns deleted.
View 9 Replies
View Related
Jan 28, 2007
I am trying to write a macro that will sort 2 columns of real numbers in ascending order, then merge them in ascending order into a new 3rd column.
View 9 Replies
View Related
Jan 6, 2014
Column A has donor name, column B has corresponding donation data for 2011. Column C has donor name, and column D has donation data for 2012. Column E has donor name, and column F has corresponding donation data for 2013.
Although Columns A and C and E all have donor names, there are many changes
from year to year with the addition and deletion of certain donors so the rows do not match up exactly. Is there a quick way to have the rows match up according to donor names in column each of the three columns? Secondarily, I need to sort by donation amount from highest to lowest (it can either be the total over the three years or based on any single year.
Currently my list looks like this:
2011 2012 2013
A A A
B C B
D D C
F E D
H F I
L G K
M H M
I would like it to look like this:
2011 2012 2013
A A A
B - B
C C -
- D-
- E-
F F F
G G G
H - -
L - L
- - M
Basically, I want to see the year over year giving for each donor. I've attached a sample for refrence. (I can't post the original due to confidentiality requirements.)
View 2 Replies
View Related
Nov 6, 2009
I'm trying to figure out a way ( excel 2000) how to sort data over a range of columns.
Attached is a sheet.
So what I am looking for is this:
Bottom 10 for target 1, target 2, target 3.
I can sort them indervidually, but is there a way to sort the all?
Or would I need a agent column for each target to sort?
Or maybe there is a way to sort the data so it would work out that if they are in the bottom 10 of lets say 2 of the targets but not all 3 they would still show in the bottom 10?
View 7 Replies
View Related
Dec 19, 2012
So I have several columns of data that have a location and then some numbers after it in additional columns. I need to be able to sort it so that all locations that are in both location columns are sorted first, and then any locations that are in one column but not the other column follows. Also the data associated with each column that follows the location needs to remain next to the location. Many times the columns will be of highly disportionate lengths too.
I've attached an HTML table so you can get an idea of what I am looking at, except what I'm dealing with is like.... hundreds of cells long.
I want to be able to turn this:
HTML Code:
<table border="1" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="100%" cellpadding="3" cellspacing="3">
<tr>
<td>A</td>
[Code].....
And in this 6 row example, I'd actually only be interested in the 4 letters that were in both columns. If it makes it easier, I would be okay with discarding the last rows, but would rather just have them sorted at the end, so I could check if....for example there were two different spellings of the same thing, and they should actually be included.
View 3 Replies
View Related
Apr 4, 2014
i have a data entry tab where we put names and button to sort and hide blank rows from other tabs. Problem i want to move down the number entered also. something like extend sorting but not affecting the formula on the other columns.
View 2 Replies
View Related
Jul 19, 2012
I have 3 columns of data and 30,000+ rows. Column A & B are related and column C contains some of the same values as column B but not an exact match. I'm looking for a way to sort columns A & B so that columns B & C match up and the data from column A is moved as well. For any data that has no match, a blank space should be input. Please see below.
A B C
red dog cat
blue cat dog
green mouse pig
yellow horse kitten
A B C
blue cat cat
red dog dog
green mouse pig
yellow horse kitten
View 2 Replies
View Related
Jul 2, 2014
how to sort data pasted from word into specific columns e.g.
I paste: 1,2,3,4,8 into the ref row, and it poulates as such, leaving out the columns for which there is no corresponding value:
Competency: 1------2--------3-------4------5------6------7------8
Ref: -------WR1----WR1----WR1-----------WR5------------------WR1
View 2 Replies
View Related
Jun 23, 2006
I have a column of repetitive data:
BLANK CELL
NAME
ADDRESS
CITY, STATE ZIP
BLANK CELL
I would like to be able to resort the dats so that each line becomes a column
BLANK CELL NAME ADDRESS CITY, STATE ZIP BLANK
CELL
I know I can do it 3 cells/rows at a time using the paste special and
transpose command but I have a 1000 names and addresses. How do I do it in
one operation instead of a thousand?
View 9 Replies
View Related
Jul 21, 2012
create a macro that will find and calculate based on the details of 4 columns on the same spreadsheet.
Basically, here is what I need to do:
1. Need to calculate how many QTY IN (Column F) and PALLETS IN (Column I) of a specific PART# (Column A) and LOT CODE (Column B) For example if enter a PART # and LOT CODE, it will calculate how many QTY IN and PALLET IN of that PART # and LOT CODE.
2. Need to do the same for QTY OUT (Column G) and PALLETS OUT (Column J) also.
3. Output should contain the PALLET # , QTY IN and PALLETS IN based on the PART # and LOT CODE search. I just want to simplify the search function in this spreadsheet because it is very cumbersome if you need to find how many items left for that PART # and LOT CODE.
Attached is the spreadsheet..
View 9 Replies
View Related
Oct 14, 2008
I have a spreadsheet with two tabs (Customer Survey Data) and (Customer Rollup). The data starts on line 5 (headers in rows 1 thru 4) and is found in columns B thru J. In column B I have dates with no blanks. In column C I have customer names - no blanks. In column J is are the customer comments - not all customers made a comment so there are blanks.
I created a macro that autofilters the data to show only those entries within a specific date range (dictated by dates found in L1 and L2 - begin and end dates respectively) by column B (no problem with this). The macro also auto removes all rows where there are no comments (column J = no blanks). This leaves me with only those surveys recieved within a specied date range that have comments.
Here is my problem: the Customer Rollup sheet is the finalized report. I need to copy only the customer names and their comments into the finalized report. The following is the macro I put in which only half works and I have no idea why. It copies the names fine...but then only copies the first comment.
Oh yea...I don't want to copy the WHOLE column...only from the first visible row down to the last. Btw...I put a lot of notes for myself...I capitalized the notes where I think the problem is...Any suggestions on how to fix it or why it just doesnt seem to work like it should?
View 5 Replies
View Related
Oct 18, 2012
i have the following macro that copies data from sheet2, sheet 3, sheet 4 and consolidates it into "master" sheet.
Code:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Dim ofs As Long
If Sh.Name = Sheet2.Name And Sh.Name = Sheet3.Name And Sh.Name = Sheet4.Name Then
ofs = CLng(Mid(Sh.Name, 2)) - 1
Sheets("Master").Range("C9:BB400").Offset(ofs * 500).Value = Sh.Range("C9:BB500").Value
End If
End Sub
i am not well versed with macros
1) the macro is not going to sheet2, sheet 3, sheet 4
2) how can i make my range dynamic. for example, my range is C9:BB400.
-can i make it so the range is C9:BBxxx (where xx is the last active data cell in the range?)
it will prob take a pro a few seconds.
View 3 Replies
View Related
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
Jan 4, 2010
I have a spreadsheet for a couple hundred rows of data, and 6 columns. The fifth column contains a date. What I need to figure out is this:for every 3 rows of data, if the date diffes in column 5 (E), highlight this row and the previous 2 rows
Currently, I've been doing this all manually, row by row - needless to say, it takes me a few hours or depending on how much other work I need to do, a few days.
View 9 Replies
View Related
Jun 6, 2014
I have a workbook has a "Summary" worksheet. I would like to be able to do the following:
(1) Sort columns D, G, J, M,... (the number of columns are different each time I run the macro that I have).
(2) create a combo box to be able to the data (that sorted in 1) by month (1,2,..., 12).
see the attached file
View 5 Replies
View Related
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
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
May 14, 2014
I currently have the following macro running to set a chart's data values:
Sub C3Quarter12013()
'
' C3Quarter32013 Macro
'
'
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Values = _
[Code] ......
When I copy the tab and change some of the data within the cells, I want the macro refer to the chart on the current tab and the values in the current tab - as currently it refers to only "Chart 2" and the values in the tab 'Figure 2 - WE OPH'.
I've tried changing the sheet name to ActiveSheet.name but that doesn't seem to work.
View 4 Replies
View Related
Sep 1, 2009
My question, and it's my first one here, is regarding the use of ListViews in Excel forms. I need to populate it with a range of several columns with data from a spreadsheet.
View 4 Replies
View Related
Dec 5, 2008
I am trying to count the # of rows in a spreadsheet in which there is non-blank text data in 2 separate columns. For instance, if the spreadsheet looked like the one below (dashes just for formatting purposes):
NAMES-------THIS-------------THAT
Mary-------some text--------some more text
John-------<blanks>---------just text here
Sue--------just some here-----<blanks>
Dave-------something--------something else
Adam------<blanks>-----------<blanks>
The total # of rows with something in both the "THIS" and "THAT" columns above would therefore be 2.
View 4 Replies
View Related
Aug 13, 2007
I need that the active cell in each worksheet of an excel file to be the cell A1( i.e. when I open a file/ a worksheet the cursor is in cell A1)
View 11 Replies
View Related
Nov 6, 2012
I'm creating an Excel template to reduce data entry errors on a file various departments submit with data, and have the VBA code associated with the spreadsheet close to done with one exception - restricting the active worksheet rows to just those with data to avoid having a number of rows with just comma separators. I can find the VALUE of the last active row (see code below,) but I'm trying to find the address of that last active cell, so I can plug that into my ActiveSheet.Range statement. Here's my code. As you'll see, I have the ActiveSheet.Range statement with hard-coded cell addresses. The A8 is the first row following spreadsheet headers, etc, but I'd like to be able to substitute a variable for the ending cell.
Private Sub CommandButton1_Click()
Dim Rowcount As Integer
Dim offset_num As Integer
FileName = ActiveSheet.Range("B4").Text
'Code to find value of last active cell
With Sheets(1)
MsgBox .Range("C" & .Cells(.Rows.Count, "C").End(xlUp).Row).Value
[code]...
View 5 Replies
View Related
