Delete Rows, Formatting, Loop

May 15, 2009

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.

View 9 Replies


ADVERTISEMENT

Store Row Number Within Loop And Delete All Stored Rows After Loop?

Sep 11, 2013

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.

View 4 Replies View Related

VBA Loop And Delete 15 Rows

Sep 10, 2013

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

Count
Campus
Name
Course
Start Date

1
336001
Student1
1
9/2/2003

[Code] ............

View 1 Replies View Related

VBA Code - Loop Through And Delete Rows

Oct 12, 2013

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

104527059 0010 00000000 09/25/2013 HSW01 0.000 0.4 HR 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 PP01

104523849 0010 00000000 09/25/2013 HSW01 0.000 0.2 HR 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 PP01

[Code] ...........

View 5 Replies View Related

Loop Over Rows And Delete Row If Value Not In Table

Aug 11, 2006

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?

View 3 Replies View Related

Delete Over 3000 Rows In Worksheet Without A Loop

Nov 15, 2008

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:

View 3 Replies View Related

Macro To Delete Mutliple Rows On A Loop

May 20, 2009

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
¬¬¬¬¬¬¬¬¬¬¬¬¬

View 14 Replies View Related

Loop To Delete Rows If Middle Column Less Than 1

Nov 20, 2012

What I have is 3 columns, all containing numbers

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"

View 5 Replies View Related

Delete 5 Rows With Loop Without Throwing Up Error?

Dec 31, 2012

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

[Code]....

View 3 Replies View Related

Find Data And Delete Remaining Rows (For Each Loop)

Apr 24, 2009

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.

View 2 Replies View Related

Using Conditional Formatting To Delete Rows

Jul 26, 2007

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
.........................

View 9 Replies View Related

Macro Script To Delete All Rows Hidden Using Conditional Formatting Color

Oct 6, 2011

After red filling certain cells based on some conditional formatting, I apply a filter to hide them. Now I need to delete them using a macro script.

View 9 Replies View Related

For Each Loop To Delete Row W/ Value (Loop Backwards)

Aug 2, 2007

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

View 9 Replies View Related

If Loop Checking The Formatting Of Cells

Feb 10, 2009

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.

View 2 Replies View Related

VB Macro, FOR Loop With Conditional Formatting

Feb 25, 2010

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:

View 2 Replies View Related

Loop Through Cell Formatting Numbers

Oct 20, 2006

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)

dash = "-"

NewItem = first4 + dash + second2 + dash + next3 + dash + last4

View 4 Replies View Related

Loop To Select And Delete A Row

Jun 21, 2012

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

[code]......

View 5 Replies View Related

Entirerow Delete - Loop

Oct 3, 2008

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.

View 9 Replies View Related

Loop To Delete Empty Row

May 12, 2006

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.

View 6 Replies View Related

Loop To Delete Row When Criteria Is Met

Jul 13, 2006

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

View 2 Replies View Related

VBA Command Loop Through Spreadsheet Rows Until Blank Rows

Apr 22, 2012

Using excel's text to speech I've put together a basic spreadsheet.

[URL]

Code:
Function talkit(Speech)
Application.Speech.speak (Speech)
talkit = Speech

[Code]....

View 9 Replies View Related

For Loop And Delete Entire Column

Oct 1, 2012

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.

View 9 Replies View Related

Delete Row Based On Cell Value - Loop

Oct 22, 2012

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.

View 3 Replies View Related

VBA Loop To Search And Delete Certain Cells

Feb 24, 2014

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

View 2 Replies View Related

Loop Through Worksheet Delete Row Until Specific Value

Jul 8, 2009

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

View 9 Replies View Related

Delete Row And Loop Based On Condition

Jan 9, 2007

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

AU300AUS1019
AU300AUS1019
AU300AUS1019
AU0000LIFHB3
AU300AUS1019
AU300AUS1019
AU300AUS1019
AU300AUS1019
AU300AUS1050
AU300GPTC011
AU300GPTC011
AU300GPTC011
AU300GPTC011
AU300INTE018
AU0000LIFHB3
0
AU300SUNQ027

View 4 Replies View Related

Delete & Merge Columns,Delete Rows With Filter, Etc

Jul 15, 2009

1. Remove J,K,N,A Columns,

2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy

3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value

4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only

5.Merge Column B,C,D,E (SYMBOL.EXPIRY_DT.STRIKE_PR.OPTION_TYP
respectively )

View 3 Replies View Related

Loop And Delete Ranges Between Page Breaks

May 14, 2013

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.

View 8 Replies View Related

Run / Loop Through Cells And Delete Based On Criteria

Mar 4, 2014

I am trying to run (or loop) through a column of cells, and if cell contains certain text (e.g. ALPHA, BRAVO, CHARLIE) and delete the row if found.

My macro as I run it:

Sub DATA_TEXT ()

'Number of cells to loop through (I am unsure how the .xlend works!)
For i = 1 to 2500

'If function defining the criteria.
If cells (1, i) = "ALPHA" OR "BRAVO" OR "CHARLIE" Then

[Code] .....

View 4 Replies View Related

Loop Through List And Delete Files From Certain Folder

Jul 2, 2014

Say I have a list in column A

Image1.jpg
Image2.jpg
Image3.jpg
Image4.jpg
Image5.jpg

In VBA, how can I loop through the list and delete files from a certain folder. The list will vary in length from time to time.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved