1. Delete all data after row 51.
2. Find the average cost (column 4) and delete all rows where the cost is below average. Thus only retaining rows with an above average cost.
3. Then I want to conditionally format any number in column 6 which appears more than three times.
4. I want to loop this through all worksheets in my workbook.
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code: for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row) if x = y then *storedRow = rowNum end if next rowNum *
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.
I have output from mainframe that is copy/paste to an excel workbook. I need to have VBA loop and delete the 15 rows. This is mainframe output so it will always be 15 rows
I use this code to delete some rows, how I would modify it to work in another spreadsheet where it would "Loop" through and delete rows that start with "User:Kellcyna" down to where the rolls start with "Numbers", and delete the rolls that start with "Total cost center" down to where the rolls start with "Numbers".
The data can contain up to 50000 rolls at times.
Sub Finally() Application.ScreenUpdating = True [a:a].AutoFilter Field:=1, Criteria1:="=" [a2:a65536].SpecialCells(xlVisible).EntireRow.Delete If [a1] = "" Then [1:1].Delete ActiveSheet.AutoFilterMode = False [Code] ........
Here is a sample of the data I need the macro to work on. The rows I need deleted are the rows that are highlighted.
User: Kellcyna STANDARD HOURS BY COST CENTER Date: 09/29/2013 Time: 15:10:04 Page: 10
Comments:
Order Op Emp Post Work ctr Setup Unit Planned Earned Total Actual Actual Actual Total Total Orde C R
# Date SU Unit Plnd Stds Setup Run Tme Brd Tme Prod Run Time E
I have two worksehets. The first worksheet contains data on products. The product code is in column A. My second sheet contains a list of product codes in column a. I want to delete all rows in worksheet 1 where the product code doesnt exist in the list in worksheet 2. Can someone provide a outline for a macro to loop over sheet one and look up the value in worksheet 2 and delete the row from worksheet 1?
I have anywhere from 3000 to 10000 rows to delete in a a number of worksheets, but would like to avoid using a loop as even with onscreen update turned to off like this:
I need to be able to run a macro that will find the word “report“ within a cell in column A. Once found it I then need it to delete that row, the row above and then the 9 rows underneath it. Once it has deleted that data I need it to then move to the next and so on.
It’s a report I have dropped into excel which repeats the page header. E.g the following is repeated constantly which I need to be deleted:
¬¬¬¬¬¬¬¬¬¬¬¬¬ Report: xxx Company: xxx Turnover By C 01/04/2008 To **THIS REPORT Customer: 0 T ¬¬¬¬¬¬¬¬¬¬¬¬¬ Account Cust Num ¬¬¬¬¬¬¬¬¬¬¬¬¬
What I want to do is delete a row if the middle column is less than 1.
However my loop seems to skip a row if the column data is like the below
a b c
1 1 1
1 0 1
1 0 1
1 1 1
1 0 1
End
Code: Range("A2").Select Do If ActiveCell.Offset(0, 1) < 1 Then Range(ActiveCell, ActiveCell.Offset(0, 2)).Select Selection.Delete Shift:=xlUp End If ActiveCell.Offset(1, 0).Select Loop Until ActiveCell.Value = "End"
I have some code that is working great except that it throws up an error on the last run through because it doesn't find any more rows to delete. I've tried "On Error GoTo" but it doesn't catch it. It seemed to work on my home computer which is running Excel 2010 but then on Excel 2007 I get the debug screen and I don't want that to show up for other users that I share the code with.
Sub DeleteHeaders() Dim lastrow As Integer lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).row
In the code below I find rows containing "$$ #" at the far left and process it. For all the remaining rows I want to delete the entire row, my macro only deletes some rows. Obviously I am not grasping something about looping throught the rows to delete unwanted data.
I have a worksheet that I use Conditional Formatting to check the dates in a total of 5 columns. In those columns, if the date is before 8/1/2006, it changes the cell color to red. Is there a formula I can put into Condition 2 to that would then clear out the whole row if within those 5 columns there isn't a red cell? This would take hours of work off of my slab.
******** ******************** ************************************************************************>Microsoft Excel - Credetnialing Master.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutAT1= ATAUAVAWAXAYAZBA1LicenseExpirationInsuranceExpirationDEA_ExpirationStateStateDrugNumberStateDrugExpirationAttestationDateNPDB_Date212/31/20078/2/20071/31/2009*229312/31/20073/7/20073/26/2007312/31/20075/1/20082/28/2010*253812/31/20077/6/20067/24/2006412/31/20078/26/200712/31/2007*256312/31/200712/12/200612/14/2006512/31/200710/19/200712/31/2009*276812/31/200711/28/200412/8/2004612/31/20079/25/20078/31/2007*303012/31/20074/6/20064/17/2006712/31/200712/3/20077/31/2009*315212/31/20075/3/20055/11/2005812/31/20078/2/20078/31/2008*323612/31/20072/28/20073/26/20079**10/31/2009****7/7/20031012/31/20078/10/20073/31/2008*353512/31/20074/11/20064/24/20061112/31/20072/1/20083/31/2009*365012/31/20 .........................
For Each loop can be instructed to loop starting the bottom of the range. I know that a For To Loop can handle looping from the bottom up,
Sub Filterout() Dim c As Range Dim rng As Range Dim i As Long Dim lrow As Long Dim counter As Integer lrow = Cells(Rows.Count, 3).End(xlUp).Row Set rng = Range("c2:c36")
For Each c In rng If Left(c.Value, 1) "~~" Then c.EntireRow.Delete End If Next c
I am having trouble with several IF..THEN..ELSE loops in my code, all of which are pritty much the same. I need to find rows that are formatted in a certain way, in the example below this is a clear background and Black text. I know the loop works because i have tested it without colours on a test workbook, and i know the colours are correct, but it keeps skipping over the condition, regardless of the outcome. All the variables shown are declared correctly before the loop is entered.
I have a complex sheet where rows are continuosly overwritten and new data needs to be formatted each time. My macro works great aside from the fact the conditional format formula expression adds double quotes rendering the conditional format useless. After running the macro I can go into the conditional format field, remove the quotes, and the formatting formula works as expected. So...I first did a "record macro" to grab the code for conditional formatting:
I looked though the site with the search and saw some things that came close, but no cigar.
I have a column of numbers formated in a constant string
eg 1234121231234
The number needs to be formatted into four sections
eg 1234-12-123-1234
I have the code to put the dashes in, but I am looking for the loop statement to take it down column C reading in each number in the 1234121231234 format until the end, and replacing it one by one with the 1234-12-123-1234.
' calls the function to put the dashes in the NSN in the QRL
'First 4 of NSN first4 = Left(NSN, 4) 'Second2 of NSN nsn9 = Right(NSN, 9) For Y = 1 To Len(nsn9) second2 = Left(nsn9, 2) Next Y 'Next3 of NSN nsn7 = Right(NSN, 7) For Y = 1 To Len(nsn7) next3 = Left(nsn7, 3) Next Y 'Last4 of NSN last4 = Right(NSN, 4)
Below is the loop. An error occurs at Rows("y:y").Select
The y value that would be found in cell C500 would always match the row that would be selected and deleted. For example if cell C500 had the value 13, I would want Rows("13:13").select and then deleted.
Sub Macro2() ' ' Macro2 Macro ' ' For y = 1 To 100
Within my loop I would like to delete an entire row - but as my loop goes down each row one by one as soon as one row is deleted the next row moves up and is then not included in the loop.
Dim Rng As Range, Last As Integer, Dn As Range Sheets("ttool").Select Last = Sheets("old ttool").Range("J" & Rows.Count).End(xlUp).Row
Set Rng = Range(Range("J2"), Range("J" & Rows.Count).End(xlUp))
For Each Dn In Rng If Dn "" Then Last = Last + 1 Sheets("old ttool").Rows(Last).Value = Dn.EntireRow.Value Dn.EntireRow.Delete End If Next Dn
If I run it 4 times it completes as it should. Or if I change it to entirerow.clearcontents all rows are cleared.
How do I make a loop to delete a row if the cell A is empty. Example: If cell A3 was empty I want it to delete the row. If cell A4 had data I would like it to skip that row and go on to cell A5.
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 two worksheet. One worksheet (ws1) contains a list of item I want. The other sheet (ws2) contains multiple columns where the header (row 6) is named by item name.
I have the following code which deletes the entire column if the header name is not in the list contained in ws1 :
VB: Sub delete_col() Dim wanted As Boolean Set ws1 = Workbooks("test1").Sheets("aaa")
[Code]....
First of all, this loop does not work properly since deleting the entire column shift them on the left, so when I first analyze column 11, if I delete it and then analyze column 12, the real column 12 now became column 11 and so on...
Secondly, this code is pretty slow. I am pretty sure I don't have to loop through my initial item list everytime I do Instr on a new column.
I have this setup successfully for the deletion of columns and have modified the below to apply to rows, however, I am not seeing the results I expect (or actually anything).
Code: Sub Analytic_RemoveNA()
'Remove NA Dim j As Long
For j = 35 To 4 Step -1 'Rows 35 to 4 If Cells(9, j).Value = "NA" Then Rows(j).Delete Next j End Sub
I have a data set that spans from B4:I32. If column I has "NA" in any row within that dataset, I want to delete the row.
I'm trying to create a function that will search for a certain value and if that true, delete certain cells in the same row. So far, I've come with the following and not sure how to write particular line.
So this function will go through column G and count the rows since it could have 1 or 50,000. So for example if column G2 has a value anything other then "Pending" then I need cell T2:Z2 to be deleted.
I know this line is the source of error Rng("T & Rows.Count:Z & Rows.Count").Select but I don't know how to express it.
Sub Check_Status() Dim iLastRow As Integer Dim Rng As Range iLastRow = Cells(Rows.Count, "G").End(xlUp).Row For Each Rng In Range("G1:G" & iLastRow) If Not Rng.Value = "Pending" Then Rng("T & Rows.Count:Z & Rows.Count").Select Selection.ClearContents End If Next
I'm trying to loop thru the worksheets(which are imported from a web form in Outlook) and delete row 31 until the text "PARTS" is reached. I need to do this to format each sheet exactly for export to an Access database. The code below works when I step thru it but when I save and run from a button it gets stuck in an endless loop. Any help would be greatly appreciated. JB
Sub DeleteRow31() Dim w As Worksheet Dim rng As Range For Each w In Worksheets w.Select
Do Until Cells(31, "B") = "PARTS:" Range("B31").Select Range("B31:K31").UnMerge Range("B31").Select If Not Cells(31, "B") = "PARTS:" Then Selection.EntireRow.Delete End If Loop Next w End Sub
i have a set of data as below and wish to delete ENTIRE ROW if cell is the same but keep the first entry when code comes to it..ie in the data set below excel would keep first row and delete 2nd,3rd,5th, 6,7,8 and then go to next unique identifier which would be AU0000LIFHB3 this would not be deleted as it is unique the would proceed to AU300GPTC011. this process would then stop when no data was available
I have a report that contains several rows of data. This data is separated by page breaks, so for example, there could be 1500 rows, separated into 90 pages by page breaks. Each range may have anywhere between 35 and 45 rows, but some of those rows may contain no data.
I would like to loop through the cells column A, look for a certain criteria, then delete the range around that certain cell that is contained within the page breaks.
I have code that will loop through the cells in column A. I also managed to create code that will delete a set range but since the ranges aren't always a uniform number of rows I need another fix.