I have large worksheet with data. Have data in the range L121:ED490.
I need to VB macro to check cells in column L,P,T,(every 4th column) until row 490 column ED and see if the cell is blank. If any cell in column L,P,T, and so on until column ED is blank then i need to make corresponding cell in column N,R, V and so on blank. Example : If L151 is blank then need to make N151 blank. If T152 is blank the need to make V152 blank.
I simply want to delete the last 10 rows containing date from a worksheet. No criteria other than they contain data anywhere in the row.
View 4 Replies View RelatedDelete every cell in the entire worksheet that = "Mail"
View 9 Replies View RelatedI have a spreadsheet that has thousands of rows of data with maybe 6 columns.
I would like to remove the duplicate rows but the problem I am facing is that I would like to use column A as the criteria.
There are some duplicates in column A but the issue is that the data in column B, C, D, etc may not be the same for each duplicate.
Easier to see in this image:
http://img127.imageshack.us/img127/6364/datajv6.jpg
So A1 is the same as A6
A1 has data in B1 and C1, no data in D1
A6 has data in B1, C1 and D1
I need to delete either row A1 or row A6, it is irrelevant which row it is but one of them has to go.
I have a sheet of data. The data starts in row 4 and can be variable in length. I want to delete rows that do not meet a specific criteria, for example a location, Leeds, which is in column "i" of the table. I have written the below but it seems to delete data from row 1 rather than row 4 and repeat down each row
Code below
Sub deleterows()
Dim i As Integer
Dim intCounter As Integer
Dim strLocation() As String
p = Range(Cells(4, 6), Cells(4, 6).End(xlDown)).count
[Code] ..........
I'm using the following on the Worksheet_Activate event to update rows with the date, (Cell AD1 contains the current date):
Code:
Range("X2:X2000").Formula = "=IF(IF(ISBLANK(W2),,$AD$1)=0,"""",IF(ISBLANK(W2),,$AD$1))"
Is there a way I can amend it so it only updates those cells which currently have no data?
At the moment it is changing every cell with the date which overwrites existing dates - I want it to update the new stuff only.
I want to scan all sheets in a workbook and to delete a first row where a value in cell(1,1) is "table".
Sub DeleteFirstRowInWorksheet()
Dim SheetName As Worksheet
Dim i As Integer
For Each SheetName In Sheets
If Range("A1") = "table" Then
Rows("1:1").Select
Selection.Delete Shift:=xlUp
End If
Next SheetName
End Sub
It delete only in an active sheet. What's wrong?
I have some VB code, courtesy of OzGrid and Davc4, that works well to delete duplicate rows based on criteria in Column A of the active worksheet (albeit a bit slow on large files).
How do I modify the code below to evaluate duplicate data in Columns A through D? .....
I am looking for a formula that either accepts the number in a cell if the numbers on two other cells are "approved". In other words, I have on cell A1 my value to be tested. On cells A2 and A3 I have two numbers. On cell A4 I'm looking for a formula that copies the number on A1 if A2 is bigger then 0.25 and A3 is smaller then 0.35. In not, there's no copy or a N/A appears.
View 3 Replies View RelatedI would like a macro to find the columns named "apple" and "peach" and delete them. These would always be in row 1 but would always be in different column letters which is why I want the macro to simply find these columns by their name and not by their column letter.
And yes, I do mean the entire column altogether, shifting entire columns to the left. Wipe it off the face of the earth
I need a quick macro that will search column D for the words "balance forward" then delete the entire row. The macro needs to loop through many rows and it contain more than one "balance forward" reference.
View 9 Replies View RelatedOne more thing and I think I completed my project. I have a tab called comments.
Column A and B will always have data in them from 1-320.
Column C may or may not have data in it from 1-320.
Is there a code I could tie a button with that will delete the cells A and B if C has no data but leave it alone if C has data?
Ex: [URL] ........
When I click sort, only A1, B1, C1 and A5, B5 and C5 will still be there and everything else deleted.
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.
I have the code below. and it works to delete the variable in DelWC which is selected by a combobox. Combobox is populated by Column R info, You select the data, and then hit the delete key, it deletes what it found, great.. but I need it to delete addition info that is in the next row adjacent to it...
Column R Column S
AINA2 X-5900
AENE2 X-5874
The code finds and delets the AENE2 but doesn't delete the X-5874
With Worksheets("Data"). Range("R:S")
On Error Resume Next
Set Del = .Find(DelWC)
If Del = Del Then
Del.Delete Shift:=xlShiftUp
I have had no luck searching for an answer so better to ask. I am using auto filter on Sheet1 and filtering a column of dates. What I need is for the criteria for the auto filter to be pulled from cell H1 on Sheet 2. Is this possible or is auto filter the wrong direction to go? I am looking to find all the rows on Sheet 1 that contain the date found in cell H1 on Sheet 2. Then I am going to cut and paste that data to another worksheet. I think I have all the rest of it figured out but am stumped at the filtering portion.
View 9 Replies View RelatedI have a big data sheet. I have approximately 2170 rows and 50 columns. In some rows I have a series of consecutive zeros. I need to build a formula that gives me the date ( located in row3) right before the first zero in the series of consecutive 12 zeros.
For instance in row 1340 I have a series of 12 consecutive zeros. The first zero happens in column AC, therefore I need the formula to retrieve cell AD3. however every rows has zeros located in different cells therefore I need a general formula.
I need cell A1 data deleted once the date in B1 has passed.
View 7 Replies View RelatedI am trying to use VBA to move data from one column to another while using values of a third column as a reference for where each value in the first column should be moved to in the second. I will try to explain as best as possible, currently I have a macro that copies from one column to the required but due to the irregular spacing of the data it results in the data not matching with the data its being copied into.
Say I have Three columns A, B and C. I have a range of data in column A say dates that have irregular amounts of spacing between them. Column B is empty. Column C contains Data that is again spaced out irregularly and not in line with that of A. The value of column C cells is just identical values. Now the aim is to move the values from column A in the order which they are listed and place them in Column B next to each value in Column C. So say the repeated value in column c is EXAMPLE then everytime EXAMPLE appears in column C i want to move one date from column A into column B next to this EXAMPLE value, in order which it appears in column A. So the first date will move next to the first EXAMPLE value and the second date which appears next to the second EXAMPLE and so on and so on until it reaches the end of the sheet.
My thinking was placing the A column data in an array and doing some sort of sort.
I have a bunch of data in column A like this:
BBX3838
BBX3838
BBX3838
BBX3838
BBX4434
BBX3249
BBX3249
BBX3249
BBX3249
BBX2233
so then using this : ...
I need to be able to search column y and rows 5-100 and where cells contain "A" copy and paste entire row to row 110. I am guessing it is really simple but I am obviuosly missing something
View 3 Replies View RelatedI've got a menu in which the user has a checkbox with 3 choices.
Choice 1 = Red
Choice 2 = Blue
Choice 3 = Green
My macro looks at what the user selected and then goes through a column on the spreadsheet - for example say the user only checked Green. In this case, a variable would be set to 001 (if they checked blue and green the variable would be 011...). The then macro goes through coulmn x and whenever there is a "Green" in column x, it selects the row, deletes it, and evaluates column x's next row.
My problem is, that I'm not deleting all of the rows with Green in column x. I've set breaks throughtout the code and the variables are all set correctly, I've checked for spaces before/after "Green" in the rows not being deleted, and nothing seems to work.
Here's my code:
Case Is = "001"
Do Until IsEmpty(ActiveCell.Value)
If ActiveCell.Value = "Green" Then
ActiveCell.EntireRow.Delete
End If
ActiveCell. Range("A1").offset(1, 0).Select
Loop
I have two work sheets I'm using. Sheet1!A column contains a list of customer names like "Smith, John".
Sheet2!A contains the same list of name with additional information found in the row. For example, you will find "Smith, John" in Sheet2!A3 and John's phone number in Sheet2!B3
In Sheet1!B it to find the associated telephone number based on matching criteria from Sheet1!A and Sheet2!A
For example, Sheet2 has the following data.
[Code] ....
On Sheet1 I have the following names. You'll notice some of the names repeat.
[Code] ......
In column Sheet1!B I would like it to scan column A from sheet2 and display the data show in column B. So it would look like this-
[Code] ....
You'll notice some names repeat but it always displays the correct number from Sheet2.
delete rows if two conditions are met .THe sample data looks as shown below:
I need to check for a given resource where the allocation hours are highest against which project , and then delete the rest of the rows for that resource records.If the Allocation hours are the same , then the priority column should be considered the highest priority should be included and the rest to be deleted.And if the priority is also the same then need just one record to remain rest of the rows to be deleted.I have priority of business area ranging from
Resource NameResource IDProject NameBusiness areapriorityAllocation hours
Lavanya 123243 MarketingSales 145
Lavanya 123243 Sales Sales 1150
Mukund 5678 Re engineeringRetail 210
Mukund 5678 StreamliningGeneric 990
Mukund 5678 documnentationDocumentation 850
Formula below works for one string, "review" but i also need it to work for "audit" "project" and "done" too.... so essentially someone can go into the sheet - hit a button and remove all these lines in one go.
[Code] ......
I would like to obtain the below issue using VBA. I would like to delete the row if the criteria hasn't met. For example,
If cell "B3 = Dec-12" and if cell C3 is not equal to "31" then delete the entire row.
If cell "B4 = Feb-12" and if cell C4 is not equal to 29 then delete the entire row.
If cell "B5 = Feb-13" and if cell C5 is not equal to 28 then delete the entire row.
This need to be applied for the last row in column A.
Basically I am looking if column B has month and Year (i.e. MMM-YY) and if column C doesn't have that particular month days then the entire row has to be deleted.
I have rows like below in Column A. I wish to cycle till row 700 and delete rows where Right(A2(,1)="X") and also Rows where there is a blank Cell.
5
22X3
65
260X
70X5
8X66
873X
2206
059X
088X
90X8
2283
7606
I have the following table, which starts in Column A, row 2. I would like to have a macro, that deletes some rows after certain criteria.
If Column F = SO normal and
Column E = aa, bb, cc, dd, ee, ff then delete the whole row.
GeoRgn refSub Rgn refCountry Country 2GroupENNR3NxaaSO claim-inENNR3NxbbSO claim-inENNR3Nx222SO claim-inENNR3Nx222SO claim-inENNR3Nx222SO claim-inENNR3Nx222SO claim-inENNR3NxaaSO normalENNR3NxbbSO normalENNR3NxccSO normalENNR3NxddSO normalENNR3NxeeSO normalENNR3NxffSO normalENNR3Nx111SO normalENNR3Nx111SO normalENNR3Nx111SO normalENNR3Nx222SO normal
I am using this to delete rows from my excel sheet. I basicaly start my script by placing xxx in cell B2000. I want to optimize my code and maybe some how tell excel do until last cell. I am playing with:
"Do Until lLastRow = Range("B" & Rows.Count).End(xlUp).Row"
But not shure how to procede with this.
Range("B1").Select
Do Until ActiveCell = "xxx"
If ActiveCell = "Ticket" Then
ActiveCell.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
I have an excel 2007 file, which is quite big: ~10mb. There are situated plenty of columns with coordinates. I would like to transcribe it, modify those data, but it will take me ages. I'm not acquainted with macros, but I heard that people can do with them everything using computers' abilities. I'll abridge my whole problem.
I give you a sample of that file, so please open it. In the file you can see two 'columns'. Every column has two other columns called x and y. As you are thinking now these two columns are containing arguments (in x column) and their values (in column y). Every third column is empty. In attached file you see, that arguments are repeating and values are increasing by one. Sometimes there is an argument without repeats, and sometimes there are only two repeating arguments.
What I wanted to do is delete those repeating arguments leaving arguments with the highest and the lowest value. Example: Please look at range A230:B232. There are three repeating arguments and for this case I should delete row 231 from columns A&B and then move cells up to join separated columns. There are also not repeating arguments, which I need to double. If there will be only two repeating arguments I would like leave them. In the end I should have a clear view for all those coordinates.
I have a have excel problem Im trying to figure out. I have 2 sheets. Sheet1 and Sheet2. I want to copy into sheet1 cell B2 the contents of sheet2 column C row x iff sheet2 column B row x = 1. There will be only one cell in that column equal to 1, so I will be copying that cells(colB; rowx) adjacent cell (colC;rowx) contents. This is what I have so far but it doesnt work. =IF('Sheet2'!B:B=1,DONTKNOW WHAT TO PUT,""))
View 5 Replies View Related