I am trying to add a macro that finds a dynamic range (number of rows changing with different data sets) sorts column B, then column A, moving blanks to the bottom. My dilemma is as follows:
I have a template with formulas that cannot be changed (I cannot paste special values, or clear the cells). Column A includes HLOOKUP formulas for cells A14:A120. Column B includes VLOOKUP formulas for cells B14:B120. The different data sets I pull in have different numbers of lines, which is why I need to maintain the formulas. However, I cannot seem to find a way to accurately sort column B first from smallest to largest and then column A from smallest to largest. In all data sets there will be at least a few blank lines that I need to move to the bottom. However, due to the formulas within the cells (column A returns a value of 0 and column B returns a value of “ “), excel does not recognize them as being blank. Here is a sample data set.
Branch
S&C Group
77770000583-DOWNERS GROVE BRANCH
MWBF2
77770000585-CHICAGO RIVER BRANCH
MWBF1
77770000587-WEST TOWN BRANCH
MWBF1
[code].....
In this case, I would like St. Charles & Algonquin to be at the bottom because column B is blank.
I'm creating an excel sheet that is linked to several others. Basically my salesmen will enter their new prospects into their own sales sheets and that will transfer via a link to my main one. The problem I'm having is mainly with my sort.
When the information is transferred, I need the sheet to auto sort the Company names alphabetically and move any blank cells to the bottom.
I'm using links to pull our sales prospects from our salesmen's individual sheets into one master sheet for reference. Once the prospect information arrives in the sheet, I need it to sort alphabetically by customer name so that we can see which customers have been visited and don't visit the same customer twice.
So far in VB, I've been working on this
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A5:L" & Cells(Rows.Count, "L").End(x1Down).Row).Sort Key1:=Range("B6"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub
But it's not sorting the way that I need to. All I really need is for the sheet to sort automatically by Company Name and for all of the 0's to go to the bottom. They are 'blank' data that will eventually be imported from the salesmen's individual sheets as they encounter new customers.
I have a file called "registrantExport" with "_great north", "_new england", "_new england", etc. I'm trying to sort them by Division and then by Last Name. Here is the macro I've tried to make, however it doesn't work properly, as it only applies to great north. How could I make so that it works for any worksheet I apply the macro to?
I have a formula which sorts data so all data that has EUSTON in column C will be shown first and then then all data which has EUSTON in column D once its been sorted.
=IF(C1="EUS",0,1)
What im trying to do is to be able to add BLETCHLEY so it does the same thing.
Data consists of a series of ranges from columns A:N and rows varying in number between 3 and 30.
There are two blank lines between each range of data.
Each range is (with exception) numerically ordered down column H (e.g, 1, 2, 3, etc).
Problem: there are around 1100 occasions when a range contains a row of data which is to be disqualified from consideration. On these occasions the data always appears in the top row of the range and is identifiable in column H by the nine codes 111, 222, 333, 444, 555, 666, 777, 888, and 999).
I wish to find a macro which will :
1. where the top row of a range in column H is one of the codes 111 to 999, transfer that row of data to the bottom of the range.
2. delete the now empty top row from which the transfer was made from.
3. insert a new blank row at the bottom of the range below the row which the data was transferred to (in order to maintain the two blank rows above and below each range).
I have a macro built that sorts my data by column in descending order depending on what column of information is selected from the drop down menu.
However I need the macro to sort column F in ascending order, and continue to sort the other colulmns in descending order when selected.
I can't get my macro to switch the sort from descending to ascending order for this one column. Looking to see if this is possible, and if so what macro formula I would use.
Here is what the macro i am currently using:
Sub ddSort_Click() Dim iDdSortVal As Integer Dim CurrSheet As String Dim lFirstSectionRow As Long Dim lLastSectionRow As Long Dim strCol As String Dim lRptHeaderRow As Long Dim iRptMeasColumn As Integer
Below is an extensive macro that basically writes data from a user's template to a certain data sheet (in the form of rows) depending on their "service group." There have been 3 separate instances of a user saving data and instead of adding rows onto the bottom of the entire list of data, the sheet only has their data and no headers/filters. I believe it is somehow deleting all the previously written data that was on that sheet.
I should also mention that this is a shared workbook. I specifically created separate sheets for each "service group" to eliminate the possibility of users overwriting each other's data (Perviously 60+ users were saving to the same sheet and running into conflicting changes errors. Now the max users writing to one sheet is 10-12). Since it is not occurring every time, I am not sure where the error is. Could it possibly be in the sort or delete sections in bold below?
I am using Excel 2003.
Code: Sub SaveData_Test() '--------------------------------------------------------------------------------------- ' Procedure : SaveData_Test ' Author : Julie/Cecil ' Date : Fri, 1/13/12 ' Purpose : Modified Julies code to capture date entries ' associated with hours logged per category. ' Check notes attached to "sOp" string comment ' for additional details.
I am generating sets of data in Sheet2 to be used for Sparklines in Sheet1. These Sparklines will sit in Column D alongside other pieces of data. The data needs to be sortable by Column A which is a list of Cities.
When sorting my data, Z-A for example, all the relevant data sorts just fine but the Sparklines do not move with the rest of the data.
Is there a way to make Sparklines move with Column sorts?
I am doing a financial statement for a person but I did not create the workbook. The book has two sheets linked together. I am overwriting the names and numbers from last year's form. I have run out of lines on the first sheet and don't know how to copy and paste the macro to the bottom of the sheet so that I can carry on with inputting my numbers. Here is a sample of the workbook.
Naturally, the first column contains someone's name. The other columns contain XY coordinates. These coordinates start at 375 and end at 499 on both axes.
I'm needing the points to show the member name and xy coordinates in the popup when hovering over the point.
How do I accomplish this?
Here's some sample data.
MEMBER X Y JOE 397 397 JIM 421 384 JANE 467 406 JEFF 389 395
I need a macro or some idea on how to sort the following numbers (I hope this makes sense!)... The problem is with the zeros get sorted to the top (or even if I have blank cells sorted to the bottom) and I need them to ignore the zeros and sort according to the value in their column but not any other column. However, I need them to be sorted in order from D, C, B, A.
A B C D 0000 4000 101000 0540 0050 6660
ie I need them in the following order.
A B C D 0000 4000 0540 0050 6660 101000
Therefore they need to sort based on the following columns.
This is because I am charting the values in a column clustered chart that need to be in ascending order.
Possible to create a macro that would detect end page (jumping from page 1 to page 2 for instance) and add a bottom border at the last row of the page.
I have attached a sample where I highlight in green the end of the page and added manually the bottom border (I did it only for the first end pages).
Referencing the spreadsheet below, I'd appreciate some help with a macro that finds the last row of data in a spreadsheet, and fills in all the data from cell C22 through H23. There are many spreadsheets like this one in my workbook. The last line of data is usually at a different line for each spreadsheet....
I am attempting to write a macro so that when a user selects enters a particular selection in a cell, it will automatically move the entire row down to the last row and shade it in a different colour.
I have data I extract out of a datasource. I run a few excel scripts upon it and export the content to another spreadsheet. The column of data (I have columns a - h) i'm concerned with is H. The totals in that column regardless of the length of the column (could be 10 rows of data or 10,000) I need to delete the top 10% and bottom 10% rows completely of those numbers in H and leave the remaining 80% in tact.
I have a 23 sheets in a workbook and in each sheet i have around 1200 to 2500 data.
What i want is : from active sheet i want to open another sheet which is located at my desktop and should select column A, H & I all data (whether it is blank at last not to worry) and paste it into my active sheet in column A,B & C then i also have formula to calculate saving per shoe in column D,E & F.
For column D,E & F in active sheet the formula should fill down until the column A gets blank (For example : in column A,B & C under active sheet takes 1230 products so formula of the column D,E & F will end at 1230 row)
I have tried to record macro for the same but this is only for the restricted rows i.e. no. of row so I post it now.
I am having a problem with a custom funciton I am trying to create. It will exit after it is finished with the IF Then Else statement. I need it to continue onto the Do loop at the bottom that does all the work.
Function UPCECheck( num As String) As Long Dim CheckNum As Long Dim TempCheck As Long Dim X As Long Dim Holdtxt As Variant UPCECheck = 0 CheckNum = 0 Debug.Print Len(num) If Len(num) = 12 Then Holdtxt = num ElseIf Len(num) < 12 Then Holdtxt = "000000000000" & num Holdtxt = Val(Mid(Holdtxt, Len(holdtext) - 12, 12)) End If...................
I have many rows of data 6 columns wide. I want to be able to enter data into a specific section, then run a macro to "cut-and-paste" that data onto the bottom of my existing data (with one empty spacer row between the new and existing data)
Here's what I have so far:
[Code] ....
Basically the part I need working on is changing [ Range("A101:F130").Value ] to be dynamic. For the code to determine the last row of data, move 2 rows down, and paste the block there.
I have a column of data that comes from simple division formulas, E2/F2. I am wanting to take the Percentages given and quickly recognize the ones in the bottom quarter.
100% 75% 23% 39% 50% N/A 100% 5%
8 items - the lowest 2 (the bottom quarter) should "stand out" - 5% and 23%
I figured two options: 1.) Conditional Formatting - don't know what the formula would be to use 2.) If statement - preferred method.
I would like in the cell next to the percentages for there to be a blank cell if the amount is not in the bottom 25% and if it is I would like the cell to read "Yes" I thought something along the lines of =if(G2 in range(G2:G100)is bottom(.25),"Yes","") Of course that is not real code. I'm hoping someone will be able to edit that to make it functional. Or guide me in the right direction. And you may have noticed the "N/A" in the list above. I would like excel to view this as 100%, at the least just ignore it and don't give me an error.
if it is possible to write/record a macro that will automatically add a summary/total row immediately below the data that is created from an advanced filter. (XL00)
The following code is doing what I want it to do in part, its copying the data in range k3 to q3 to the next empty row in columns a to f
It works when I change any cell in column K (11), I want to change to only on a change in K7 or K8? As an afterthought if I'm dealing with the same sheet do I need to make reference to sheet2?
VB: Private Sub Worksheet_Change(ByVal Target As Range) Dim nextRow As Long If Target.Column = 11 And Target.Cells.Count = 1 Then
I have a multi tabbed page and the page i want to sort (2) is looking up the 1st page and creating one column list from cell a1 to a280 and it includes a number of zero's from the look up pages because that is the correct result. I already have it sorting but I want it to sort and leave the zeros at the bottom (they are currently at the top) of the sheet so I can just print the info I need.
Can I create this in sort or do I need a macro and how can i write it as I am very green on VB.
I'm looking for a simple piece of code I can use to manipulate the data on the attached workbook. Firstly, I need to be able to sort the data by column O ("Balance"). I then need the code to extract all the information for the top 10 and bottom 10 rows and paste the info into a new worksheet. The code will need to account for the fact the number of rows might increase/decrease with this report.
My macro works fine but I'm interested in seeing if I can speed it up. What I'm doing is starting at the bottom and comparing it with the row above and if they match in font color it will fill the top with orange and delete the bottom. This works but usually takes several minutes.
Public Sub ADMINCompareList() Dim varTest1, varTest2 Dim lng As Long, i As Integer, iTest As Integer Application. ScreenUpdating = False Worksheets("ADMIN").Activate For lng = ActiveSheet.UsedRange.Rows.Count To 2 Step -1 If Not Range("M" & lng).Font.Color <> Range("M" & lng - 1).Font.Color Then Goto newrow End If varTest1 = Intersect(Range("J:W"), Rows(lng)) varTest2 = Intersect(Range("J:W"), Rows(lng - 1)) For i = 1 To 14....................................
I have just started trying to learn VBA as its pretty cool but I am getting stuck on how to filter by non-blanks. It's probably something easy:- here is what i have got so far, but its not working
With ActiveSheet .AutoFilterMode = False . Range("A1:V1").AutoFilter .Range("A1:V1").AutoFilter Field:=22, Criteria1:=0 .Range("A1:V1").AutoFilter Field:=5, Criteria2:="<>" End With
