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
ADVERTISEMENT
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
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
Apr 23, 2013
I'm okay with Excel, but I'm just getting into utilizing VBA and I've been searching high and low for a simple VBA code that will remove/delete rows based off a single criteria without loop as there are over 40,000 rows. I tried a couple that I found onilne, and adapting them to my criteria range, but no luck. (All the ones that I found that work use loop and it takes about 15 mins to run through the entire spreadsheet)
I would like to maintain my first row as it's my headers. My single criteria is to remove all rows that have "NO" in column D.
View 9 Replies
View Related
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
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
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
Dec 9, 2006
I have the attached spreadsheet set up using a vlookup funtion in D1. I need the macro to be able to find the actual row this number resides in and delete that row whenever E1 is equal to Delete.
View 5 Replies
View Related
Jun 22, 2007
I currently have a spreadsheet with circa 50,000 lines with info regarding our websites at work. In every row, column "A" contains a unique reference number.There eill either be 1 row for this reference number, or there will be 2 rows. there will never be more than 2 rows for each reference. I wanted some code to say that if theres only one row, then delete it but if the if the cell underneath it is the same then its ok. I tried the following but it deletes the row anyway, supposing the cell under it is the same or not
Sub test()
Dim icell
icell = ActiveCell.Offset(1, 0).Value
Range("A1").Select
Do
If ActiveCell.Value = icell Then
ActiveCell.Offset(2, 0).Select
Else
ActiveCell.Rows.Delete
End If
Loop Until ActiveCell.Value = ""
End Sub
View 4 Replies
View Related
May 26, 2014
I need a code to delete all rows with "Complete" in Column D below Row 7.
View 2 Replies
View Related
Jun 22, 2012
I would like to search my worksheet and whenever it finds the word "Description" in column "B" then it should select the range "B:E" for that row and delete the cells directly above it.
This is what I tried but nothing happens:
lr = ActiveSheet.UsedRange.Rows.Count
For Z = lr To 1 Step -1
If ("B:" & Z) = "Description" Then GoTo deleteit
GoTo xt
deleteit:
z1 = Z - 1
Range("B:" & z1, "E:" & z1).Delete
xt:
Next
End Sub
View 1 Replies
View Related
Oct 21, 2013
I have a spreadsheet broken out into about 30 4-row ranges, for example (A10:L13). All of the ranges use a using a vlookup argument:
=IFERROR(VLOOKUP($A10,Fund_Range,2,FALSE),"") in the second row.
Around 15 of the ranges will be populated based on varying data in the fund_range data table, the remainder will be blank. The first row of each range is a header row, I would like the macro to delete the range when there is no value in the second row (the blank ranges). Every attempt I have made starting with other code from the forum has come up empty. I think part of my problem is the vlookup formula existing in these otherwise "blank" rows.
View 2 Replies
View Related
Jul 6, 2014
I am looking to make a cell with the Value =Today()-1. I then want the code to pick up the cell value and then delete any row that does not have that value.
So for Example it would only keep =Today()-1 Dates in the sheet and delete the rest.
Code:
Dim LR As Long, i As LongWith Sheets("Sheet1")
LR = .Range("Q" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
If .Range("Q" & i).Value = "Y" Then .Rows(i).Delete
Next i
End With
View 9 Replies
View Related
May 4, 2006
I need a way to check to see if the first four characters of cell A1 is = 2006. If it is, do not delete the row, else, delete the row. Have tried everything I can think of.
View 5 Replies
View Related
Feb 22, 2014
I have this sheet(sheet2) with some data , I have 2 macro, my problems at this time is that I am looking for macro that delete only the blank rows that under the rows with {SELECT ....} and the under the blank rows with {CELL-ENTER......} and all the rest blank leave in place , I am also looking for option to integrate the new macro with the existing one and come up with one macro that I can refer to click button , in case that it is unfeasible , it's ok with me , I just will call the other macro at the from the first macro that I have.
View 4 Replies
View Related
Dec 28, 2009
I have a spreadsheet with date and time values of the format
"dd/mm/yyyy hh:mm" in column A followed by some other data in cells of that row.
What I'd like to do is have a macro that will delete a complete row if the time value in column A lies between two times that I can specify in the macro (the dates are irrelvant)
View 8 Replies
View Related
Jun 7, 2012
I have two listboxes where I can move tickers from left to right. This is synced with a sheet, such that the tickers also are pasted there too.
But if I use the remove button, I can remove tickers from the listbox, but I cannet delete them from the sheet. If I understand right the listbox only refer to what row the selected is, not what the text is...
And then its a bit tricky. I have tried a code where it just add the list one more time, after the itmes has been removed. And that could work if it hadn't been for the second column which also has a text, this column is updated after every ticker has been moved from left to right.
What I would like to have is a function that would know what the text in the row that I delete, or remove.
This is what I have so far:
Code:
Private Sub cmdMoveToLeft_Click()
Dim i As Integer, j As Integer, k As Integer
Dim RowsStart As Integer
Dim LastRow As Long
Dim Rows As Integer
RowsStart = Me.ListBoxX.ListCount
[Code] .......
View 4 Replies
View Related
Dec 13, 2013
I have got a vacancy tracker spreadsheet and I need it to move an entire row from the 'Open' to the 'Closed' sheet based on the status in column K, i.e. 'Closed +', 'Closed -', and 'Closed + Achieve'. Once this has been done I want it to delete the entire row in the 'Open' sheet. At the moment it just cuts the entire row and then it is left blank and when I delete it manually the macro stops working completely.
Also I can only get it to move 'Closed +' and 'Closed -' and seem not to be able to add a command to move 'Closed + Achieve' rows.
This is what I have got so far:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("K")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Value = "Closed +" Then
[Code] .......
View 2 Replies
View Related
Feb 14, 2008
I am using conditional formatting to apply a light green color (index number 35).
Is there a macro that can delete the cell contents of the cells with this formatting in col K and L?
View 9 Replies
View Related
Dec 11, 2008
Let's say that I've got 125 sheets. The 1st "Reference", and the rest sheets names are "1" to "124". I need a macro to delete sheet based on cell range A1:A300. If the range contain 25 data so the number of sheet will be 26 (Reference and 1-25)
View 9 Replies
View Related
Jun 21, 2009
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.
View 9 Replies
View Related
Mar 9, 2007
I used this macro to find the duplicates in column B:
Sub KryDups()
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell. Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell <> ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True
End Sub
The duplucate cells are now red in color. (RGB(255, 0, 0)). How do I now code VB to delete the rows in column B where the cell color is red? Here is some of the code that I tried:................
View 5 Replies
View Related
Aug 29, 2007
I have merged two workbooks into one. What I need to do at this point is to delete all rows that have a duplicate entry, basically anytime the cell content in one cell matches the cell content in the cell right below or above it, BOTH rows should be deleted. At this point, this is above my VBA skills so I'm asking for help in how to do this. The stripped version of the workbook is attached (only 100 rows) but in reality this is a huge workbook with almost 22,000 rows.
You will notice in the attached workbook, that cell contents for A2 and A3 match. For what I need to do, I need both rows (2 and 3) to be deleted. If you go down a bit, starting in row 89 all the contents in column A are unique so those need to remain.
View 3 Replies
View Related
Feb 6, 2008
I have a standard data import that populates data in columns A through DC. I am trying to delete those columns from BX through DA where the value in row 2 equals 0.
View 4 Replies
View Related
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
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
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
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
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
Mar 28, 2014
i am looking macro formula to delete row with 2 criteria :
- delete rows if yellow color background and based on adjacent cell (blank cell), otherwise keep rows when adjacent cell not blank
View 8 Replies
View Related
