I need some code that will keep one row, delete 5 or 6 rows keep one row and repeat the process over and over again until the end of the data.
like this:-
1 keep row
2 delete row
3 delete row
4 delete row
5 delete row
6 keep row
7 delete row
8 delete row
and on, and on......
The problem sheet attached to this thread. I would like to extract all rows belonging to one person to another sheet named after the person and also their status.
For instance, I want a VB script which extracts all rows with Mark when his status is A/V to another sheet named as Mark and likewise for Steve.
I have a calendar-template that contains entries - little text-chunks - and i also have a excel-sheet that contains 365 texts - those have to get imported into the Calendar-template. - see the attached files for more infos. Play around with the calendar-template.
Note: the attached data contain the following
a. the calendar-template (attachment a)
b. the excel-sheet - with the 365 text-chunks that have to get imported into the template (attachment b)
What is aimed - i want to have a weekly calendar for my own usage
To see what we already have - take the calendar-template.
You see the calendar-template contains the fully-fledged view of the second week in 2014.
If you jump to the filed A 2 in the calendar-template (attachment a) - here you see the date - actually the 2014-01-06
You can play around and see that the greek verses are already included for the whole year. If you choose any date of the year - the greek verses in the template already are added.
What is missing - are the additional text-chunks that are represented in the excel sheet.(b)
note. i have colored the fields in the calendar-template and the excel sheet with corresponding colors so we can see - which fileds have to get filled with which text-chunks of the excel-sheet. Additionally the calendar-template contains the fully-fledged view of the second week in 2014.
Question. can we do a automated version?
Attached files:
a. _calendar_2014_week_2_demo_excel_version - this is the calendar template
b. _words_2014_365_days_excel_version - this is the additional text sheet that contains the 365 text chunks - slogans
I am using an autofilter to delete chunks on unwanted data, by using Range(Selection, Selection.End(xlUp)).Select however it picks up the header row. Is there anyway I can either get the selection of data to select one less line, or a way to get it to leave the header line?
View 2 Replies View RelatedI have a worksheet in which lot of information is entered on a daily basis, so far for the year there are 350 rows which I scroll up and down or autofilter etc to look at older info. I have created a button to make it easy to always return to the cell that I have been working on whether I have scrolled up a few hundred rows or used autofilter. Although I always want to keep autofilter on as it is needed to make other code work. The code I am trying to use is...
View 2 Replies View RelatedI have a problem with my formula. As you can see, i want to get the CIM number of the Team Leaders (TL Name) when i change the dropdown list of the campaign. But what im getting is the repeated CIM numbers.
Here's my formula:
=IF(ISERROR(INDEX('Team Data'!$F$2:$F$5489,SMALL(IF('scorecard (OM Search)'!$C$12='Team Data'!$E$2:$E$5489,ROW('Team Data'!$E$2:$E$5489)-MIN(ROW('Team Data'!$E$2:$E$5489))+1,""),ROW(A1)))),"",INDEX('Team Data'!$F$2:$F$5489,SMALL(IF('scorecard (OM Search)'!$C$12='Team Data'!$E$2:$E$5489,ROW('Team Data'!$E$2:$E$5489)-MIN(ROW('Team Data'!$E$2:$E$5489))+1,""),ROW(A1))))
I want to sum two cells, doesn't sound that difficult but I want to be able to change one of the cells over time. Hence, if B1 is the total sum cell, and A1 is what I fill in. B1 starts at 0, and if I fill something in in A1, B1 will sum itself and A1 (Unfortunately this gives me a circular reference).
So what I'm looking for is some way to use paste special automatically in the formula B1 I guess.
It is possible and how?
I want to merge two cells repeatedly, down a row for an entire tab. For instance merge A1 and B1, merge A2 and B2, merge A3 and B3 and so on. I feel like there should be an easy way to do this but I cannot figure out a way other than manually going through each row and do not know how to write the macro. Sadly I have about 500 rows in 10 sheets so doing this manually would be.
View 2 Replies View RelatedI have a situation where I want to copy the first 6 rows of data in a worksheet down 11000 times and right below the previous. I can get it to copy down, but I can't keep the formatting the same, and the formatting is important for when I print. After I get this to copy down the values and format, I'll be inserting pagebreaks every 6th line.
I have the following code right now:
Sub PasteDownFormat()
Dim x As Long, i As Long
x = 11000
If x < 1 Then Exit Sub
With Range("a1:l6")
For i = 1 To x
Cells(i * 6 + 1, 1).Resize(.Rows.Count, .Columns.Count).Value = .Value
Next
End With
End Sub
I have the following information in a cell. I want to get the exchange:ticker which is inside the brackets to be returned (coma delimited). How can I do this? One thing to note here that the number of companies in the cell can vary
Banco Bilbao Vizcaya Argentaria (CATS:BBVA); Banco Santander, S.A. (CATS:SAN); Banesto Banco Espanol de Credito SA (CATS:BTO); Banco Popular Espanol SA (CATS:POP); Caja de Ahorros y Monte de Piedad de Madrid; La Caixa Group, Asset Management Arm
I have a peice of code (Below) and instead of going from work sheet 4 to the one in the workbook 2 from the end sheet by sheet running the same bit of code it just loops repeatedly on one sheet??
Sub a()
Dim n As Integer
For n = 4 To ThisWorkbook.Sheets.Count - 2
Columns("W:W").Select
Selection.Copy
Selection.Insert Shift:=xlToRight
Columns("L:L").Select
Selection.Delete Shift:=xlToLeft
Range("L8:L9").Select
Selection.AutoFill Destination:=Range("L8:W9"), Type:=xlFillMonths
Range("L8:W9").Select
Application.CutCopyMode = False
Next n
End Sub
I need creating a tool for numerology.
Every alphabet from a to z has a number associated with it.
A = 1, b 2 , c = 3 etc
Numeric Values for Each Numeral
A1N5
B2O7
C3P8
D4Q1
E5R2
F8S3
G3T4
H5U6
I1V6
J1W6
K2X5
L3Y1
M4Z7
I can either use excel or access or write this.
Example 1 , r = 2 , I = 1, L = 3. .
If I write RIL in column 1 , column 2 should say 213 and column 3 should add 2+1+3 and display 7.
Example 2:
Col 1 = Infosys Ltd
Col 2 should say 1587313 344
Col 3 = 1 + 5 + 8+ 7+ 3+1+3 3+4+4 = 39 , 3+ 9 = 12 , add 1+2 = 3
The spreadsheet has over 200K rows and two dozen columns. My job is to:
1. There are two columns, one is called OCR code where we have repeating ID's and other column is called Minutes Count which is basically a number. I have to filter the OCR column for similar OCR codes(values) and then add their time(Minute Count) so that I get total minutes for a specific OCR code. After adding the min I will copy them into a new sheet with the code in one column, total minutes in another and number of repeatitions for that specific OCR code or count number in a separate column. Example:
OCR_Code; Minutes
54xg; 456
45fk; 65
23IS; 18
54xg; 971
45fk; 265
.
.
.
Now I've to filter 54xg so that I can only see it and its corresponding minutes. Then I will add up all min for 54xg. Then I will count the number of instances a 54xg has been repeated in spreadsheet(here its 2 but it can be in hundreds in my case). Last thing is to transfer all this data to a new sheet. And automatically repeat the process for other OCR codes like 45fk, 23IS etc...
I have a macro called "ResultCopyPaste". When it is run it copies and pastes some results. I have a cell C23 that counts how many more results need to be run, copied and pasted. As each result is copied and pasted the count changes. When the count gets to zero nothing else needs to be done.
I want to create a macro that will repeatedly run the "ResultCopyPaste" macro until C23 shows zero. What is the VBA code? Presumably something to do with repeating the macro if C23>0
In case it's relevant the code for the ResultCopyPaste macro is:
Sub ResultCopyPaste()
'
' ResultCopyPaste Macro
[Code].....
I am trying to find certain words in a column and delete the word and characters following. For example, Say I have a column of info as seen below. This is a test of me. I am just experimenting with this stuff. Deleted (6/15/01) Let me know what you think. I am not sure about it all, but I guess I will figure it out. riviledge1 (01/05/06) Now let's see what happens when I try to test it.
I want to find all the "Priviledge1 (01/05/06)" and replace with nothing. Please note, the date will change with each record, so I need to figure out how to tell Excel to find "Priviledge1", delete it and the date behind it. So I want to delete "Priviledge1" and the next 11 characters including the space.
i have created a user from called "delete"
from this the plan is that you will be able to select the name of the tab that you wish to delete from a drop down list (which will contain all the tab names) and click on a command button and this will then delete it
I am in need of code that will match a variable, "TheName", to the values in a range named "Phases". If a match is made then I want to delete the entire row which the value is matched in.
example:
PHASES
cat
dog
pig
My variable, "TheName" = Cat
I want the code search only the range "Phases" and delete the entire row in which the variable "TheName" is found. so in this case it would delete the entire row in which the value of "cat" was matched.
In Excel 2007, when I delete a row/cell, text is associated with deleting row/cell but some lines, polygon ... can not associate with deleting row/cell . So the lines, polygon ... in mistake location.
View 12 Replies View RelatedI have data in a column that has numbers letters and characters in, all that I require is the first 9 numbers (these are always the first 9 characters), any idea how this can be achieved.
View 5 Replies View RelatedI m writing a formula that will highlight duplicates. I want to use the supplier code (column D) as the search criteria. can the formula identify these duplicates by entering the word 'duplicate' in column L.
View 3 Replies View RelatedI have a long list of names with their locations in brackets. What formula should i use to delete the brackets and their contents only?
View 3 Replies View RelatedI would like to run a macro that will delete the selected row in a protected worksheet but only delete it if the row does not have locked cells. I want users to be able to delete the rows they no longer need but only be able to delete them if the row does not have formulas that are locked.
BTW-I am working in an older version of Excel.
The loop cycles through my sheet correctly but wont delete any cells.
View 10 Replies View RelatedChecks if there is a sheet name and if there is a sheet of that name then it is deleted.
the sheet name is a number entered through an inputbox and "Average"
Just finished amending a piece of code to enable me to search for an agents name in a list of files. This is then copied to a new worksheet (named by the name entered in the search). What I need is to cut(or delete) the row from the original sheet (sheet 1 in this case) thus leaving me the remaining list of files.
Below is how the code is setup at the moment....(just copies the row)
Deleting Columns. I've got 2 codes here,
This:
I have a column that has all the digits in the phone number. What I would like to do is remove the first 8 characters example 111-222-3333. I want to remove all the ones dash twos and dash. Can someone help me please ?
View 7 Replies View Relatedi need is a script that will add two boxes together to get a sum in a third box, but where it is tricky for me is that i need the third box to retain the summed value even when either of the first two box values are deleted.
For example, say i have box A1=10, B1=40, and C1= SUM of A1 & B1 (so C1=50). Easy enough, but where im lost is that i want to be able to delete the value of either A1 or B1 and then have C1 still = 50, so then i can input another value into either A1 or B1 to continue to add to C1.
In column G of my excel 2003 worksheet named: Data, I have a list of people's first names. These names were extracted from an external database.
Some of the first names actually contain peoples second names or partner's names.
Example:
John & Jill
James and Sue
Kerrie-Anne
Joanna Lyn
I need a macro to please delete people's second name.
So in the example above I need to keep all names that are joined by: & or and or - (such as John & Jill, James and Sue, Kerrie-Anne).
Also, in the example above I need to delete all names that are joined by a <space> (such as Joanna Lyn).
I have an excel spreadsheet that contains about 1000 rows and about 25 columns. The file contains employee information, name, id, cost center, department, title, FTE...etc. Column E contains the cost center which is a 7 digit number (i.e. 8001234). Within the 1000 rows of data there somewhere to 70 cost centers. I would like to delete all rows where a cost center does not match a list of 13 cost centers. I'm thinking I need some kind of array where I type in the 13 cost centers in the code, but I'm unsure of the syntax within VBA.
Also, the second step of the code I'm looking for is to delete all columns except for Column B, E, J and L. Those, by the way are Name, Cost Center, Job Title and FTE.