Delete Row Based On Cell Below

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


ADVERTISEMENT

Delete Row Based On Cell Value

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

Delete Row Based On Cell Text?

May 26, 2014

I need a code to delete all rows with "Complete" in Column D below Row 7.

View 2 Replies View Related

Delete A Range Based On Cell Value?

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

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 To Delete 4 Row Range Based On One Cell Value

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

Delete Rows Based On Cell Value?

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

Delete Row Based On First 4 Characters Of Cell

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

How To Delete Blank Column Based On Cell Value

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

Delete A Complete Row Based On Time Value In Cell

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

Delete Value In Cell Based On Selection In Listbox

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

Cut / Paste And Delete Entire Row Based On Cell Value

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

Delete Cell Contents Based On Color

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

Delete Sheet Based On Cell Range

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

Copy, Move And Delete Row, Based On Cell Value

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

Delete Entire Row Based On A Cell Color

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

Delete Rows Based On Cell Contents

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

Delete Columns Based On Cell Values

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

Macro Delete Row Based On Color And Adjacent Cell?

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

Hide And Delete Sheets Based On Cell Input?

Aug 11, 2014

I have a situation where i would like to hide and delete a few sheets in my worksheet.

there is a few sheets where i want it to be constantly visible and one sheet is based on a cell reference.

the remaining irrelevant sheets i would like it to be hidden and deleted.

'always visible are sheets that are constantly visible in all files' 'visible msds is the sheet where my product data is being pulled out based on a input i enter on my first sheet' the sheets i want to hide and delete will be the remaining irrelevant "MSDS" that i would like to remove through macro.

View 3 Replies View Related

Delete Cell Data Based Off Expired Date

Aug 19, 2014

I need cell A1 data deleted once the date in B1 has passed.

View 7 Replies View Related

Excel 2010 :: How To Delete Rows Based On Cell Value

Sep 23, 2011

I have a massive spreadsheet (ca.110 000 rows [excel 2010). I need to cut this monster by deleting all the cells that do not have a certain cell value within them.

Say that in my column A there are different entries (text type). I would like to delete the ones that do not match the following criteria:

"mytextascriteria*", where * somehow stands for the rest of the string.

Strictly speaking I can only determine beggining of the string I want to perserve but the values at the end of that string are changing.

I found the thread about deleting entire row based on a cell value here:
Delete entire row based on one cell?

but I couldn't quite work out how to apply it in my scenario....

Otherwise is completely pointless to do it manualy with these amout of records.

View 5 Replies View Related

Delete Contents Of A Cell Based On Char Count

Jul 23, 2008

I have this forumala.

=RIGHT(A1,MIN(500,LEN(A1)))

I would like to apply this formula to VBA and call to existing macro so that this is applied to all the cells in Col. A. How can I do that? Or, is there a better way to do it?

Buttom line, I'd like to see only the last 500 characters per cells in Col. A after I run the macro.

View 9 Replies View Related

VBA Delete Entire Rows Based On The Value Of Single Cell

Jun 10, 2009

I know it can delete entire rows based on the value of single cell, I just don't know how to do it. So what I need is a macro that will delete an entire row if the value in a particular column = 0.

More details: ....

View 9 Replies View Related

Delete And Shift Cells Based On Cell Values

May 23, 2007

I have a spread sheet that has RX codes as field headers and ones or zeros as values. I want to find all cells which contain "0" and delete those cells while shifting the remaining cells left. The range of cells is aj1:dr17365 and the sheet name is "May 07 Rx HCCs"

View 8 Replies View Related

How To Delete Rows Based On Whether Or Not A Cell In Column B Is Empty Or Null

Jun 23, 2014

I am building a template ("Table") that will import data from 4 other spreadsheets and then format the data once its all in the template. I need to delete all rows where a name didn't import. The names are landing in column B (starting with B22), so I set up my code using an active cell loop macro to examine each cell to see if it was empty, and then to delete the row if it were. I've tried 4-5 iterations of code but nothing is working correctly.

Apparently when the fields are copied over from the other spreadsheets, some empty cells actually have something in them such that they are not completely blank. What syntax I can use so that I capture every instance of a blank/empty cell and delete that corresponding row? Some of the code I've tried is below.

[Code] .....

View 3 Replies View Related

Delete Columns And Rows Based On Cell Fill Color

Jan 31, 2014

I am a locksmith and attempting to write a spreadsheet app. for my line of work. The output page will use columns A-G and rows 1-?10000? depending on the size of the system. If a 5 pin system is requested then columns A+B will be blacked out. 6 pin system A is blacked out. 7 pin system nothing blacked out. Also, if any cell matches the master key that was input, that cell is filled with red. I know, confusing.

Basically, I am trying to remove the blacked out columns automatically, and to remove the enitire row is any cell in that row is filled with red. Then to remove blank lines.

View 2 Replies View Related

Delete Duplicate Rows Based On Cell/column Values?

Oct 28, 2009

I have a excel file which contains dublicate rows. The duplicate rows can be identified based on few cell/column values. I need a macro to delete the duplicate rows when the below condition is satisfied: let us consider row 5 and row 6:

If column 7,12,13,16,17,18,19,23,24,27,28,29,30 in row 5 = row 6 then row 6 has to be deleted. This condition has to be followed for all other rows in the excel used range. Have attached the sample workbook.

View 5 Replies View Related

Delete Duplicate Rows Based On Multiple Cell Criteria

Apr 21, 2007

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

View 7 Replies View Related

Filling Empty Cell Value Based On If Else Condition And Delete Row More Than 2 Cells Empty

May 23, 2014

Here find the excel file

My requirement

1) 4 values contains in each row based on the values from those cells the max value will display.

2) if more than 2 cells have empty,NR or NA text means the entire row has to delete.

3) if 2 or more that means 3 cells having values the empty cell,NR or NA cell will place value with the condition of macro that is 75% of other values which is maximum among them.

View 1 Replies View Related







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