I have a list of peoples first and last that I am trying to import into another application. My problem is that the application wants the first names in column A and the last names in column B. Right now my excel has the first and last names in the A column. I would just copy and paste, but there are 2,600! I just want to move the last word in column A to column B...
I need my macros to search for the word "Cancel" or "Cancelled" in columns "T" and "U". Once found, I need the macros to make that entire row an opaque shading.
There will be other wording in these cells that contain "Cancel" or "Cancelled". Is it possible for the macros to search in the sentence and find the words "Cancel" or "Cancelled"
I have a macro I found here on the boards written by Lenze to delete an entire row based on what is found in column A. I would like to delete any row where Col. B contains 10 or less characters and I have modified it to do so (or at least I think it does). My problem is that it takes about 12 minutes to run the macro (I have about 50k lines to run through). I was wondering if this is the fastest method or if it examines things other than just column B.
Sub Test() Dim i As Long LR = Cells(Rows.Count, "B").End(xlUp).Row For i = LR To 2 Step -1 If Len(Cells(i, "B")) < 11 Then Cells(i, "B").EntireRow.Delete Next i End Sub After this runs, I am left with Columns A to somewhere around AH. The columns are generally in the format of text followed by a numeric column. An individual text column has the same name through all of the rows. The numeric columns have varied values whether negative or positive. Ideally what I would like: If a given cell (ie. C2) in Row 2 is numeric, then copy the cell to the left (ie. B2) into (ie. C1) and then delete Column B. I need this to work for multiple columns from B to C, skip D and E, and then from F to AG (and maybe beyond).
I have a pivot that includes customer data and I need to create an average for each row based on the first populated column for each customer. After one month of units appears for the customer, I need to calculate the average going forward, and if there are blanks after that they should be treated as 0 in the average. Below is an example of my description of the pivot.
Jan Feb Mar Apr Customer #1 1 1 3 Customer #2 1 3 Customer #3 2 2
So, the average for customer #1 would be calculated from Column B to Column E, and factor column D as a 0 in that calculation. Customer 2's average would start in Column C and go to Column E, and factor column E as a 0 in that calculation. Customer 3's average would start in column D to column E.
Is there a quick way to transfer the values from a column to a row? If so, does this have to be for the entire column or can you select part of the column and transfer it to a row?
it is possible to move a row of cells with (names in each cell) to a column. Basically, I want the list of names to go from top to bottom rather than left to right.
From this (each name is in separate cells) John Jones Bob Smith Jane Morgan
I have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.
I have an excel program that is supposed to count word instances in a word document. I can't seem to find the right declaration for a word document. For example to declare a workbook in excel its
Dim wb As Work Book
I've tried
Dim doc As Word.Document 'or Dim doc As Word.Application
as shown in some of the forum posts, but an error user-type not defined keeps displaying.
At this point, I have a list in the first column that looks something like the following:.......
First, I want to delete any cells that have two or more '' characters and delete any cells that do not have any text after the first '' character. Then I want to take the text before the character and put it in column B, deleting the ''. Once I have done these things, my original post and this one can be marked solved.
I need to move a number of rows, based on user input in front of the first column, continuously until it 'flattens out' into one row.. And then do the procedure again for the next row Kinda hard to explain.. maybe i'll use an example using this matrix :
I need to move data from columns B & C into A without losing current data in column A (see attachment). I'm sure I know how to do this but cannot for the life of me remember how.
I use this macro to open a hyperlink in "column B" of the next row. However, it only works if I begin the macro from "column N" on the line above. (the hyperlink is always located in column B)
I want to be able to run this macro from any cell on the line above. How to modify it?
ABCDEFGHIJKLMNO 1ActiveURLWhatDateFirst NameLast NameOtherOther2Other3Street1CityStateZip 2XLinkData112/21/2011BobSmithData2Data3Data4123 MainMooresvilleNC28117 3XLinkData112/22/2011LarryJonesData2bData3Data4456 MainMooresvilleNC28117 4XLinkData112/23/2011MaryAkinData2Data3Data4789 MainMooresvilleNC28117 An example would be to run it while Cell "I2", "J2", or "K2" is selected and have it open "B3"
I have a situation with a spreadsheet i'm working on.
Basically, when a button is pressed I want it to search for all rows which have a "yes" in an offload column (Column AS, on the sheet "Active") (So it can be yes or no).
If it finds a yes, I need it to move the whole row in to a new sheet called "inactive", then move the remaining results up.
I have taken and played about with a different VBA code, but i'm not sure it's even the right line!
Here is what I have :
Sub Refresh() ByVal Target As Range) If Target.Column = 45 Then If UCase(Target.Value) = "Yes" Then Target.EntireRow.Copy Destination:=Sheets("Inactive"). _ Range("A" & Rows.Count).End(xlUp).Offset(1) Target.EntireRow.Delete End If End If End Sub
I have this macro listed below that finds all these files, copies cells from stat sheet and places them in sheet1 in another file. It keeps looping till all is found and done.
My problem is when it goes to paste in sheet 1 I want it to shift over a row each time.
Right now during the first pass it pastes in column B, I want it to start in column D
The issue seems to lie right under where it says "Put data into workbook"
Public Sub PullData() Dim wkb As Workbook Dim lngStore As Long Dim strDate As String Dim strName As String Dim Book As String Dim Sheet As String Dim week As String Dim Store As String
As I am at work, it does not allow me to upload the file due to restrictions on uploading. However, the spreadsheet is fairly simple so should be able to describe it.
The main sheet is called "Investigation Court Apps". An entry is made into each row and the status shows as "Open" in Column A, until a final closure date is input in Column Z, at which point the entry in Column A changes to "Closed". This is achieved with the following IF statement in Column A.
=IF(B3="","",IF(ISNUMBER($Z3),"Closed","Open"))
So that part is all automated. What I need is some VBA coding or macro (might be same thing but completely new to all this) that when the value in Column A changes to "Closed" the whole row moves over to "Sheet3". Once the row has been moved over, I want to ensure there are no blank rows also.
I have tried the following amongst others:
Sub MoveToSheet3() Dim C1 As Range Dim RowNum As Integer For Each C1 In Sheets("Investigations Court Apps").Range("N1:N" & Range("A65536").End(xlUp).Row) If Cll = 0 Then
[Code]....
I know to open VBA its Alt+F11 and to close Alt+Q, but thats about it.
in moving data from multiple columns to a single column. I have attached a sample image from an excel file which details the requirement. The first column contains a qualifier, that should remain constant when the data from columns B to the end move to a single column "B". The number of columns for each row is different, however, there is a maximum number, say 25. As mentioned in the image, when the data from columns B to the end is moved in to column B, column A is retained fixed, to the original value, and the original data below it is pushed below. Any pointers to how this can be achieved by VBA or without VBA?
Right now the code below reference AI on sheet BOM. However, I need it to be either referenced by being the last filled row on sheet BOM or by having the word "rounded" on row 4 --what which over column on row 4 that has the word "rounded".
Current code:
Range("J2").Select If Sheets("BOM").Range("C4") = "" Then ActiveCell.FormulaR1C1 = "=BOM!R[3]C[25]" Else ActiveCell.FormulaR1C1 = "=BOM!R[4]C[25]" End If
I'm trying to work out how to take all cells with data from multiple columns and stack them in a single column.
Here's the history...
I have multiple part numbers in single cells in column A. I perform a text-to-columns function. The resulting part numbers spread across multiple columns (say, B through K). Now I need to get all the part numbers, in their own cells, stacked in column A for one continuous list of single cell part numbers.
Is there a VBA option for cutting only the data cells from Column B-K and pasting the data at the bottom of column A while avioding blank cells?
I'm working on a decision matrix to evaluate different alternative production methods. Before that criteria have to be choosen and weighted against each other. The user would fill out the yellow/ blue cells with (0=less important; 1=equally; 2=more). Then values are added up and put into some kind of ranking.
The example was done by hand, which costs a lot of concentration. I would now like to extend the matrix - do you know how to fill out the grey cells more efficiently?