i have code like this to copy certain word in cell and place it at new sheet:
VB:
Sub Foo()Dim i As Long, iMatches As Long
Dim aTokens() As String: aTokens = Split("Accommodation", ",")
For Each cell In Sheets("Sheet1").Range("C:C")
[Code].....
the problem is the cell source is not deleted. what line should i change in code above to move the result to new sheet and delete row source
I want to enter a number 0 to 999 in cell A1 and when the enter key is pressed move the contents of cell A1 to A2 and A2 to A3, cascading down until A40. So after five numbers the first number I entered woul now have cascaded down to cell A5 ready to go into cell A6.
Would I have to use some kind of program or does excel have a command for this?
I am trying to find something that will let me look at a Log Book Page (a Worksheet). I need it to look through Cells E4 trough E15 I want it to look for the 1st entry of 3 different materials.
3/4" Plywood
1/4" Plywood
3/4" Particle
These names are in a drop down menu from a different sheet in the same work book
Sheet Name link '.'!
'.'!A2 3/4" Plywood
'.'!A3 1/4" Plywood
'.'!A4 3/4" Particle..............
I need a macro that will search though Column AD and delete the cell contents if the cell contains "0" (zero). Edit: I'm sorry I just figured this out using the following
Dim lastrow, j As Integer
lastrow = Cells(Rows.Count, "ad").End(xlUp).Row
'I presume the last row C is not blank
For j = lastrow To 1 Step -1
'MsgBox j
If Cells(j, "ad") = 0 Or Cells(j, "ad") = "0" Then
Range(Cells(j, "ad"), Cells(j, "ad")).Clear
End If
Next
I got some data like this
CTM
KKB
ACD
TEEE.PK
ACFFE
OO.OB
What i want to do is to delete everything after the dot. And the dot itself
I recorded a macro:
Code:
Sub Makro4()
Range("A4:C15").Select
Selection.ClearContents
[Code] ......
This code is working home on my MacBook, but I wonder if you have any suggestions for improvement? I will assign this macro to a button.
I am having some trouble writing a macro to delete cell content. I know which cells I want to clear, but I can't seem to get the macro to do it.
I also want to have something in the macro that inserts data into the lines that I am trying to clear. I want cell A67 to say "Payment" and cell A71 to say "Total."This is the code I am using:
Sub DeleteCell()
Dim i As Integer
For i = 67 To 71
If Range("A" & i).Value = "--" Then
Range("A" & i).Delete
Next i
End Sub
I need a VBA that i will attach to a button that will delete spisific data in the cell.
Eg... delete all the cell CONTENT in column A1:A65536 with the word "NONE" in them, it must only delete the word not the cell.
This formula counts the number of spaces between words in a cell:
View 5 Replies View RelatedI'm looking to combine a number of worksheets into a summary sheet .. but on some the narrative is more than the 255 characters. How can I , where this is the case, insert a row below.. say input "Continued" in column A and then copy the contents of the cell which is more than 255 to the cell immediately below it.. and maybe do this again where users input more than 510 characters.
Alternatively, is there a way of getting around the copy and paste values.. where it only inputs 255 ?
I have a spreadsheet that I need to rearrange to improve readability. I have an example attache, which is easier to understand than describing it, but I'll try:
I want to start in a1, move to a2
copy a2 to b1
delete line 2 (the previous lrow 3 now becomes row 2
if a2 > 0.01, skip it and
move & select cell a3
... and repeat the above logic, on the next series of cells...
E.g.
copy a3 to b2
delete line 3 (the previous lrow 4 now becomes row 3
if a3 > 0.01, skip it and
move & select cell a4
...and repeat the sequence again.
I have a spreadsheet (Sheet 1) listing current Work Orders with each work order occuping a seperate row; Column E lists the status of the work order, with the status being chosen from a drop down list.
I would like to have a macro that will copy the entire row and paste into (Sheet 2) when the status is changed to CLOSED, and clear the contents of the cells on Sheet 1.
The aim of this being of course to have all open work orders on sheet 1 and all closed orders on sheet 2.
I have a spreadsheet with a serial number is row 1 in column A with the rest of Row 1 empty. Column A is empty in Row 2, but has the data associated with the row above in columns B through L of Row 2. Then comes 2 blank rows and the pattern repeats with a new serial number in column A of row 5 and so on. I would like to move the serial number down one row, delete the now empty first row, delete the following 2 empty rows, and then loop to do the same thing again for all 9000 rows of the spreadsheet.
View 2 Replies View RelatedI have a spreadsheet which contains 6000 rows where column A1 lists a name, B1 an address, C1 an amount and B2 the 4-digit zip code and place. Now I want to move the content of B2 to D1. I can't use offset because the spacing of the zipcode cell is not always the same.
View 4 Replies View RelatedAt the moment I have 1 spreadsheet with 12 worksheets that I have to enter data into. I would rather enter all the data onto one sheet then have a macro move the data into the correct worksheet based on the contents of columns A and B.
The raw data will be entered into the worksheet "Data". Once complete, I would like the user to press a button and the macro to then identify from column A the suppliers name. If it is not one of the recognised suppliers, then it would move the row into the worksheet "one off". If it does recognise the supplier then it checks column B to see which of the two supplier's two worksheets it needs to copy it to, with the data entered into the correct column based on the column title (ie only the white columns).
I have created a powerpoint that has linked charts from excel. Stupidly, I have saved both of these files (powerpoint and excel) on my desktop.
I need to save them on another drive (so they can be accessed by others) but i am worried that if i move the original excel file, it will break the links for the charts in the powerpoint file....
I have over 150 charts so I don't fancy going through and re-pasting each chart.
I use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell
Sub COMMENT()
Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?
Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.
Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"
I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).
I have a listbox which brings up my bets for todays horseracing. If say one of my selections is a non-runner, would I be able to add a command button to delete that selection from the source data.
The source range is "AL10:AN24". So all I would want to do is select the selection if it isn't running and click on the command button to delete the selection.
I already have code to sort the source data and would like to add this code to the new code, so once a selection has been deleted the source is also resorted.
Here is that code, in case it is needed:
Private Sub CommandButton1_Click()
Range("SYSTEMS!AL10:AN24").Sort Key1:=Range("SYSTEMS!AL27:AO41"), Order1:=xlAscending
End Sub
I have a column containing mixed text and I want to remove everything from the column except instances of certain colors; black, red, blue etc. Is there a formula which would do this or can it be done with Search/Replace?
View 9 Replies View RelatedI have two worksheets that directly link a range of cells on both sheets, ie: Output!a1 = Database!a1.
In this case, the Database sheet contains data that I want to manipulate by deleting entries that are defined in rows. however, when I delete one of the source rows I get a #REF error on the Output sheet.
Is there any way to avoid this so the entries will automatically shift down a row if a row has been deleted? :o
So if i were to delete row two on the source page and the data from row three moves down, that would, in turn, cause the row three data on the output page to move down one row as well.
Can a macro be used to:
1. Delete rows that contain certain text in a worksheet ?
2. Highlight a cell a colur based on a response ?
I have attached an example of what i mean .....
In the Audit Protocol worksheet is the main information - which contains questions and answers .... If a yes, n/a or no is selected then a response is automatically generated in the observations column (thanks to SHG for helping me with the formula for this) ..... How do I then get the cell to change to red if a NO response only is selected ?
Then the information from the Audit Protocol worksheet (the observation column and number column) are copied across to the worksheet named Action List (this is done just by the copy function) .... How do I go about deleting rows that contain "no action required" - as these are not needed for the report to be generated ?
I have a long list that has all the services from our stores. I´m using the formula below to count how many services does each store have that are over 20 days old.
=SUMPRODUCT((G!$A$2:$A$6000=$B4)*(NOT(G!$I$2:$I$6000="Valmis/Odottaa nouto"))*(NOT(G!$I$2:$I$6000="Valmis, toimitettu"))*(NOT(G!$I$2:$I$6000="Finished/WaitingDeli"))*(Päivämääärät!$C$1-G!$C$2:$C$6000>Päivämääärät!$I$3))
Now i´m wondering is it possible to make a macro that would delete all the cells in the range that do not match the criteria of my formula?
With one exception in sheet G cell A2--> has the number of the store. There are over 40 stores so each store has a number 10, 20, 30, etc...
This is the part of the formula that determines witch store it is counting
(G!$A$2:$A$6000=$B4) B4=10 in this case and B5 would be 20 and so on... By modifying that one part i can count old services for all of our stores.
Now I want the macro to delete all services from the list that are not over 20 days old and belong to the store 10 (B4). If it would delete everything that dont match that formulas criteria i could not get a list of old services for our other stores.
What i´m trying to accomplish is get a list of the services that are over 20 days old. I´m now picking them from the list by hand and that takes a really long time. Because of the number of the stores and the size of the range.
Need macro or other code that will examine a column in my my table for particular text (example: find text: "Total") and then DELETE the whole row that the cell containing the text is in.
I have this:
Sub CompileData()
'Dim rng1 As Range
'Dim rng2 As Range
'Set rng1 = Worksheets("Sheet2").Range("A4").End(xlDown)
'Set rng2 = Worksheets("Sheet2").Range("C4").End(xlDown)
'Set AllRng = Union(Range("rng1"), Range("rng2"))
'Worksheets("Sheet1").Range("A5").End(xlDown).Delete
Now when its pasted it has cells with "Nil" in it. I need to delete them, or just not copy them over.
i have data from D2:D10000
I Used data of first five rows e.g ("d2:d6") if in range ("d2:d6") any cell is blank then delete the all contents of the rows
e.g
d2 have data
d3 have data
d4, d5, d6 have no data then the contents of range ("d4:d6") should delete
I would like to delete the content only of every 5th row - I do not want to delete the row.
I have about 6000 4 line addresses in one column and between each address is the number 0. Need only to delete the 0.
My worksheet has a report name in cell A1, a brand name in B2, price name in C2, and color in D2. Row 3 has a table header in cell range A3:E3 followed by rows of table contents at the end of which is a row of empty cells. This sequence repeats itself for a new product: a row with the report name in column A, followed by a row with a new brand name, price and color in the same columns as before followed by the same table header and table contents. The number of products (with the previously mentioned details and table) vary so I would like to write a macro that is not dependent on a set number of products. Each table varies in the number of rows but never contains empty cells and always ends with a row of empty cells. I want to delete all rows with the report name and have all products in one table with the brand, price and color added to the table header and contents.
View 2 Replies View RelatedI have a worksheet in whichs Column A Cells , there is corresponding string content in Col. Z. Some cells of column Z contain of many lines (up to 100 line) which i want to reduce to one. I want to select one line through double clicking in the listbox of the userform and all other lines should be deleted and only the clicked one should remain. Code should then jump to the next non empty Z cell.
Columns B, C, D, E, F, G and H should also be shown in the userform and they should be live editable. I have attached and example file with userform and example data.
For a table like the one below produced for the sake of example (actual is much much bigger) I want to make it list rows that are true for a certain column for a certain variable in the matrix. So for say water terrain, which types of activity can I do i.e. swimming. Or for Offroad the activites which I can't do i.e. Run and Swim.
ActivityWaterRoadOffroad
Jog nym
Run nyn
Walk nyy
Swim ynn
y=yes
n=no
m=maybe