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


ADVERTISEMENT

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

Search Column A For Blank Cells And Delete Entire Row

May 20, 2009

I have tried several methods to delete the entire row if the cell in column 'A' is blank ...

View 9 Replies View Related

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

Excel 2010 :: VBA Code To Search Multiple Columns And Delete Any Duplicate Cells (not Rows)

Jun 12, 2014

My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.

View 14 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

Search For Text Delete This Row And Delete The Row For Every Instance

Apr 29, 2008

I have an formula if statement that returns "deletethisrow" if the test is true.

For every occurence of "deletethisrow" I want to delete the row. The number instances will be variable each time I run the file. So maybe it will find that string, maybe it will find 10 instances. I want to do some kind of loop that won't error out when it cannot find "deletethisrow", but will delete the rows for each instance where it does find this string.

I know it was verbose, but if I just do a loop for a fixed number of loops it will error out if it runs out of rows to delete.

View 9 Replies View Related

Search Specified Row Without Loop

May 11, 2014

I need to look for specified row in column "G" order to copy information from another workbooks into that row.

E.g. search row contains name "David"

How to simplify this code without loop as I have 100 names to search and thus I will waste my time to create "Do, Loop Until" for 100 names.

View 14 Replies View Related

Loop Through Cells And Ranges Reverse Order With Backwards Loop

Aug 30, 2006

I am looping through each cell in a range and I would like to loop in reverse order.

Dim CELL As range
Dim TotalRows As Long
TotalRows = Cells(Rows.Count, 1).End(xlUp).Row
For Each CELL In Range("C1", "C" & TotalRows)
CELL.Select
'Code here to delete a row based on criteria
Next

I have tried:

For Each CELL In Range("C" & TotalRows, "C1")

and it does not make a difference. I need to loop in reverse order since what I am doing in the loop is deleting a row. I am looking at a cell and determining its value. If the value is so much, then the row gets deleted. The problem is that the next row "moves up" one row (taking the pervious cell's address) and therefore the For Each Next loop thinks it has already looked at that row.

View 7 Replies View Related

Copy Cells In Loop Based On Loop Increment Being Multiplied

Feb 7, 2008

I have some numbers in a column that I need to copy 12 times (each one) into another column. The problem is that I got like 200 records that will be converted in 15000 aprox. I've uploaded an example of what I need,

View 3 Replies View Related

Creating A Search (loop)

Dec 15, 2011

I've got a code that stores a variable and then searches for that variable in a different file. When it finds the answer it it jumps over and up and highlights a different cell. The problem now is that I want it to keep searching past where the first result was and repeat this process down the entire column. Here's what I have so far:

Code:
Sub Find()
'
' Find Macro
' Finds a specific key word or phrase from the search box and codes the matches green.
MsgBox "This may take a moment, please be patient."
' This next line establishes the fact that the function will use a variable.
Dim FindClass As String
On Error GoTo ErrorCatch

[code]....

View 3 Replies View Related

Run A Loop And Search In Column B For A Certain Name (cut And Paste)

Sep 14, 2009

I am trying to use a code to run a loop and search in column B for a certain name and if it finds it I want it to then look at column P and if it finds that the result in column B is less than $1000 then I want it to cut the entire row and paste the row in another sheet. Here is the code.

View 4 Replies View Related

Excel 2010 :: Search Gets Into Infinite Loop?

Feb 5, 2013

I have various spreadsheets that get into infinite loops sometimes when I search for things. Hitting break breaks it, and the search window (find all) shows the same exact entry hundreds of times.

This is Excel 2010.

View 2 Replies View Related

Loop Formula To Search Through Range To Get Expected Output?

Feb 14, 2014

Trying to look for formula for searching through Range and search through Data to achieve expected res

Data Output ExpectedRange
Pune 123 is city Pune 123 Mumbai 999
Maharashtra 345 is state Maharashtra 345Pune 123
India 678 is Country India 678 Nagpur 666
Maharashtra 666 is state OthersPune 555

Below is the query i used for manual search however need formula to see through Range and achieve output expected

=IF(ISNUMBER(SEARCH("Pune 123",A5)),C5,
IF(ISNUMBER(SEARCH("Maharashtra 345",A6)),C6,
IF(ISNUMBER(SEARCH("India 678",A7)),C7,"Others")))

View 5 Replies View Related

Excel 2007 :: Loop To Search Worksheets For Specific Range Value?

Mar 8, 2012

using Excel 2007. I need a code to identify a worksheet within a workbook by cell/range value. The book is used by various users. They have the rights to add new sheets and all but delete columns in the 'master worksheet'. The sheet names can be changed by the user but I need to rename the master sheet on opening the file. To do this I have put a specific value in a cell within the master worksheet which then should allow me to find the sheet and rename it. (let say Range A1 has a value of "this sheet") I have a mental block on how i can run a loop to search each sheet for the identifying value until the range and value is found and the sheet identified

View 5 Replies View Related

Using The =IF(ISNUMBER(SEARCH Function To Search Multiple Cells)

Aug 21, 2009

I' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells.

I can get it to work to search one cell (as below):

=IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10)

(In this case the respone is No Change as Cell G10 contains "Same")

However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform:

=IF(ISNUMBER(SEARCH("Same",G10:R10)),"No Change",'Aug 09 Matrix'!F10)

Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.

View 9 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

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

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

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

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

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

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

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







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