The first line of the code chooses the columns to select; all columns until there is no value. From there I need to have it sort those columns based on row 1. The problem is that the columns chosen are variable. It could be columns I:N (as shown below) or column G:Z or any other combination. (The code below was recorded if that matters at all.)
View 2 RepliesI have a spreadsheet with column headers like, "Date" "Phone number" "Name" ect., and rows of data below. Each of these rows contains data pertaining to a prospect employee, as the purpose of the spreadsheet is to keep track of job applicants. One column is used as a 'Recruiter Sign-off' area, where the recruiter who processed the application inputs their name:
Example.png
I'm trying to automate a variation of excels sort function. However, I don't want to sort 'From A to Z.' Instead, I want to have the rows of data sorted with the 'Recruiter Sign-off' column used as the sort Key, and a selected name (of one of the recruiters) used as the sort Criteria.
I already have a userform that allows the user to select a name from a listbox. When they press confirm, the name is stored in a Public String variable, (selectedRecruiter). I'd like then like to have the rows below the column headers to be sorted based on the selected name. Example: User selects the name "Jon" from a list. The application then brings all of the applicants that 'Jon' has signed off on (those rows that contain his name) to the top rows of the range.
The workbook is shared, so I cannot use tables.
I found a code that accomplished what I want, however it also leads to a horrible system crash after a few times running it (BEX crash).
[Code] ..........
I was handed a pdf of some leads by a co-worker. Finally, I got the data into excel but it's in a pretty useless format... 30,000 rows of contact info with no rhyme or reason. I want to upload this into our CRM, but I need to get all the different bits of info into separate columns. I'm no excel pro, but I managed to use an Excel plugin called ASAP tools to bring some order to this chaos... All names have a blue cell color, all titles have a green cell color, work phone is red, and so on.
Does anyone know a way that I can sort these into separate columns? I've trying playing with the Filter function, but it hides rows which makes the output useless for my purposes.
setup:
Columns: c)6:00 AM, d)6:15 AM, e)6:30 AM
Rows: 4)tech1 Phone, 5)Tech2 Phone, 6)Tech3 Phone
What I am tying to do is to select the column that matches the time, for instance anything before 6 AM would select Column C, betweeb 6 and 6:14 still selects column C, 6:15 to 6:29 selects column D, Etc
i have a sheet for monitoring sickness. i have used a macro before which automatically selects a range and emails it to recipients when i press a button. what i would like to do is have excel automatically choose the range for me based on today's day. in the attached example i would like column "A" copied along with the 5 columns before today's date, today's date column and also the 5 columns after today's date. paste the lot into an email and send. (i have highlghted the parts i would like copied and pasted into an email based on today's date (21st july).
View 4 Replies View RelatedI am using Selection.ClearContents command to clear data from column F and G. I know the last row as 230 but the starting row is the first empty cell which I find using Do While loop in range F31 to F230. The variable that stores the first empty cell number is 'r'.
Range("F &r:G230").Select
Selection.ClearContents
I need to create a macro that clears the contents of a range of cells. However, that range of cells is variable.
I know my starting cell (C9) and I know the selection goes over to column Z every time but the number of rows I want to clear after that may change.
Also, whatever that first cleared range is, I would need to skip the row below it then clear the same number of rows below that. (not entire row of course, just the ones from column C to column Z)
How do I change the selection of a range with a variable, and not a hardcoded number in XL2003? I have to update a set of spreadsheets every month, and it's a hassle to have to constantly open my pivot table worksheet, copy, open the summary worksheet, paste ... etc. etc. etc. The code below is my attempt at creating a ComboBox with "January, February, March, etc." and every time I select a particular month it will automatically copy data from my pivot table worksheet into my new summary worksheet in the correct column. The range of data from my pivot table worksheet will never change, so I have no problem hardcoding that in, but based on which month is selected will alter which column the data goes in in the new worksheet.
I want to be able to write code for one month (say January) and then when I want to use a different month (say February) I can just change the column number and call up the originial January code.
Private Sub ComboBox1_Change()
Dim ColNum ' This is the variable I want to change based on which month is called
If ComboBox1.Value = "January" Then
ColNum = 1 ' Column number for Column A- Where my January column is
Elseif ComboBox1.Value = "February" Then
ColNum = 2 ' Column number for Column B- Where my February column is
' etc. etc. for each month
End If
Call January
End Sub
Private Sub January()
' Just a quick msgbox to make sure the previous macro is calling this one
MsgBox "Is this macro running?", vbQuestion + vbYesNo, "Check"...........................
I'm trying to write a bit of code that will allow me to clear some sheets. One of the sheets has formula for x number of rows, and these need to be left intact. Under these forumla there is an area where data is pasted, and this needs to be cleared becfore starting the process.
Colum A is blank, apart from cell A1 (the column heading) and another cell (variable row) with the value 'Paste here'. I've used this variable cell in other macros on the sheet as a marker, so need this to stay. I was going to use this code below, but I don't know how to tell it to select from the current active cell to the bottom of the spreadsheet.
I need an action to allow for the random selection of a predetermined series of number codes which represent a given letter.
It is probably easier to look at the attached sheet and the previous discussion to see what I mean.
Previous discussion:
http://www.excelforum.com/excel-work...-of-cells.html
I have a spreadsheet which contains our fleet information covering many vehicles in one list. The teams for these vehicles are listed in Column A. What I would like to have is:
A dropdown box to pick from All, or the various teams in A1To hide every row that doesn't match the selection of A1To reveal every row that contains the selection for A1 at any point in column A. e.g: "*Dog Team*"
This is because something might be listed as "Moved from Dog Team" or "Dog Team, temporarily on loan", or "Dog Team V66.m"
I have taken a small sample of the data I will be working from and made a very simple drop down box which displays the pages of information for the vehicles by hiding/unhiding blocks of columns:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Range("A2") = "MOT/Service" Then
ActiveSheet.Columns("A:P").EntireColumn.Hidden = False
ActiveSheet.Columns("Q:CE").EntireColumn.Hidden = True
ElseIf Range("A2") = "Contact Details" Then
[Code]....
But because there is also filters on the sheet, meaning the positions of the entries based on rows can change, I need it to search for the "*xyzzy*" method, but am unsure how to do this! While just using the Filters will work, due to some human limitations I have been asked to have a specific drop down box in a very specific location with instructions next to it.
I have been trying to determine the maximum/minimum value and additional count from a variable range which then I can use to subtract the first data of the range; I have been able to do that for a fixed range but not a variable one.
I have Column A with random positive numbers.
I have Column B with random negative numbers.
I have Column C with random numbers.
I have Column D with random numbers.
I have Column E with random 0’s and 1’s.
I have a set of 1600 cells of numerical data on each of the columns and there will be times when the random 0’s and 1’s from Column D will have appear repeatedly before changing to the opposite number, fx, I will get 7 nr. 1s before I get a 0 (zero).
I have been able to obtain the values in 2 cells within a fixed range:
If the last 8 cells in Column D have been “1” then I do:
F1=MAX(A1:A8)
G1=INDEX(C1:C8;MATCH(9.99999999999999E+307;C1:C8)) ---(Which provides the first number of the range in Column C after Column D has changed from 0 in D9 to 1 in D8)
H1=(F1-G1)
If the next 3 cells in Column D have been “0” then I do:
F9=MIN(B9:B11)
G9=INDEX(C9:C11;MATCH(9.99999999999999E+307;C9:C11)) ---(Which provides the first number of this range after Column D has changed from 1 in D12 to 0 in D11)
H9=(G1-F1)
Additional to this, the COUNT has also been challenging since I want to obtain in Column I, the COUNT of repeated 1’s from each range of 1’s in Column D; and also in Column J the same but for 0’s.
Fx:I18 (size of the range of 1’s originated from D1:D8)
J93 (size of the range of 0’s which consist to be the next range before 1’s were originated)
I12X (size of the range of 1’s which consist to be the next range before changed to 0’s)
However, that only works for a fixed range and continuous updates are made.
Book1.xlsx
I have a worksheet with columns of Unique Words and corresponding Frequency of Occurrence for several years. I would like to sort the data so the rows match up on same Unique Words across years.
I am using Excel 2007.
I am working on a new budget spreadsheet that I am creating. I want to accurately calculate credit card expenses. As everyone who has a credit card knows, charges made this month are not due to be paid until next month. To make matters more complex, charges made from (for example) January 20 until February 19 are not due to be paid until March 20. With that in mind, I created a spreadsheet with a different tab for each month. Each tab has a place to put in all expenses. Here is an example:
date
Category
Expenses
Remarks
[Code].....
Obviously, The above example is from January. IN the February tab, I want to calculate everything in the "Expense" column that has a date in the "Date" Column before Jan 20 and where it says "Kohls - Frank" in the Category column. Of course, in the March tab, I would need to grab all of the "Kohls - Frank" expenses after Jan 20 from the January tab, and everything before Feb 20 from the February tab.
I figured out how to add up everything with the following formula: =SUMIF(Jan!A:A,("<="&DATE(2013,1,20)&E32),Jan!C:C) How can I filter on the Category tab?
I want to sort columns A,C:P and use column C as the column that I sort and the other rows will move with column C. I want column B to stay and not move with the sort. How can I do this.
View 2 Replies View RelatedI have a worksheet that lists the months of the year on row 3 from columns C through N.
What I am trying to do is to be able to select the month I want from a drop down list or combobox and then hide the remaining columns based on the selection. For example if JAN is selected the months FEB through to DEC would be hidden.
I'm trying to make the below two column selections into a selected range to clear out formulas/values in the range.
Current code segments:
ActiveCell.EntireColumn.Select
Columns(ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column).EntireColumn.Select
Is there a way to combine the two code segments into one selected range?
This should be a simple question for those who have the knowledge. I am making a 2 column excel page, the first column will have an authors name and the second one will have the book name. I need to lock these two columns together so that author name and book name always stay together (side by side) on the sort command. I need to be able to sort by author or book title and I realize that it gives you the choice to expand the selection, but I can't trust that the others (kids) will realize the importance of doing so. This is going to be a very large list with hyperlinks and I can't afford to chance whether someone else will select the correct command. So a long story short. I want to build a list that can be sorted by author name or book name and be sure that the correct author will always be beside the correct book, but that are able to be independantly sorted
View 5 Replies View RelatedI have a spreadsheet with data organized into columns in sets of two - the first column is an X value (Pixel number) and the second column is the Y value (pixel density). I'm trying to make a macro that will select the first set of columns, chart it, move the chart to the next sheet, then select the next two columns of data, chart them and move the chart to the next sheet etc. I've gotten as far as having it make the chart and move it, but I can't seem to make it select the next set of columns - I've been trying different things for a couple days now.
Code:
I have a simple spreadsheet. A column for a persons name and 31 columns, one for each day of the month. I want to apply conditional formatting, either fill color or border color, to the date column of the current date when the spreadsheet is opened.
View 2 Replies View RelatedI am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this
View 9 Replies View RelatedCode to enter the current date in column if the values are entered in the adjacent column's.
View 2 Replies View RelatedI am trying to sort a range of data by 1 column (column D), and get the following error:
"The sort reference is not valid. Make sure that its within the data you want to sort, and the first Sort By box isnt the same or blank."
Having googled and also checking some previous posts, I thought the issue may be around qualifying my key to ensure that I used the active sheet, but having done this, I still get the error.
CodeRange("A2:V2", Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Overview").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Overview").Sort.SortFields.Add
[Code]....
I am having trouble with my macro. I have attached excel sheet for reference.
Basically, I want a Macro that Looks up columns B & C of current sheet and matches it with table in the charges tab, and then returns hourly pay in column D.
I have created the macro but it's giving me Run time 1004 error.
Also, my formula is incorrect. Maybe that's why my macro isn't working?
2WayLookup_Macro.xlsm
I have a vba script that matches a date on two sheets then copies a formula based on the location of the date, the problem I am having is how can I refer to the activecell and active column without typing them manually as the range will change each day. See vba script below:
VB:
Sub find_date()
Dim FindString As String
Dim Rng As Range
[Code]......
Here is my
ThisWorkbook.sheets("sheet1").Range("M").Select
For Each cell In Selection
If cell.Value = ThisWorkbook.Sheets("Sheet2").Range("A").Value Then
cell.EntireRow.Delete
End If
Next cell
Range("a1").Select
End Sub
I want to remove all rows in sheet 1 that contain any value found in Sheet2 A I using XL 2003.
I need a macro to sort on five columns (Column A, L, P, X, and Y) out of about 33 columns. Sort is all lowest to highest.
The code I have so far is:
Sub Macro1()
Range("A2:AG").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("L2") _
, Order2:=xlAscending, Key3:=Range("P2") _
, Order3:=xlAscending, Key4:=Range("X2") _
, Order4:=xlAscending, Key5:=Range("Y2") _
, Order5:=xlAscending
End Sub
However, I get an error "runtime error 1004 - method range of object global failed".
I have a pivot table with multiple row fields and multiple column fields. One of the column fields is a Date and I need some VBA that will auto-sort the columns into ascending order by the Date column field.
E.g., if the first four column labels are "2-Jun-2010, 13-May-2009, 16-May-2013, 17-May-2012" then i want the sort to arrange them as "13-May-2009, 2-Jun-2010, 17-May-2012, 16-May-2013".
Note: This is the left to right order of the columns i'm talking about, not the top to bottom order of the rows, or the data in the rows but specifically the column labels.
I've tried googling a solution and I can find a variety of code that deals with sorting the data in the rows in all sorts of ways, but nothing on how to order the columns.
i'm using excel 07, and the source data for the pivot table has the Date field formatted as custom "dd-mmm-yyyy". This can be changed if necessary.
I would like to copy selected rows in a range D19:I49 to another sheet, by clicking a button to bring up a User Form containing a List Box, seven Option Buttons (one for each sheet), an OK and Cancel Button. When the User Form is displayed, the required rows are selected in the List Box, then the required sheet is selected via an Option Button. Upon clicking the OK button it pastes (only the values) to the selected sheet, but to the next empty row (if there is already data entered previously) in the same range as above (D19:I49). All seven sheets are identical and are named Monday, Tuesday, etc. to Sunday. If the option button selected is the same as the active sheet a message box could be displayed to ask the user to select another option button and upon clicking the OK button on the message box return the user to the User Form once again so that the correct option button may be selected.
View 2 Replies View RelatedIs there are macro that will allow me to: Clear Print Area, then Set Print Area based on user selection and finally print the Print Area to fit 1 page? I tried to search for solutions, but couldn't find any that matched my problem.
View 2 Replies View Related