Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA to delete row if specific value exist in row above

Hi,

How would I create a VBA macro to check if the same value also exist in the row cell above it (e.g., column P) and then delete the second row with the same value? For example, if the cell in the row above had an "X" and the current row had an "X", delete the second row but not the row with the "X' above. This would check all rows for consecutive "X's" in column P and delete rows with the consecutive "X''s"

Thanks.


Post your answer or comment

comments powered by Disqus
I went and did a couple searches and tried to get an example I saw to work. Sadly I couldn't figure out why it wasn't functioning correctly...

In this workbook I need to hide a row if no value exists in column C through Nth for the row, I was using http://www.excelforum.com/excel-prog...-is-empty.html as my example.

Modified it so it worked on Sheet 19 and started at C6, but when I went to check it out nothing i could notice occured...

testingdemo.xls

Any way to help me get it so that the rows hide and unhide the blank rows would be extremely grateful.

Hi I was wondering if it is possible to delete rows if a value appears only 1 time in a column? To clarify since 123 only appears once in column A I want the entire row 4 deleted. Is this possible?

Sample Set of Data
Item #	   Description	                             Cs/Cnt   Exp Date   P. S.	P. E. 
64	         DEF                                     15   4/27/2009	  23	 23
64	         DEF                                     15   5/3/2009	  38	 38
64	         DEF                                     15   5/4/2009	  12	 12
123	         abc	                                  1    6/4/2009	  22     22


Please help me

I need VBA writing to delete rows, more precisely:

if i have the data as below

DE gross value
DE net value
MX gross value
VZ land
...
I'd like to delete the rows that don't begin with DE, how should I do. My data contains 4000 rows, apparently I can't do it by hand

Thanks a lot for your help
Best regards
oanh

I’m pretty new to VBA and macros and I’m trying to delete rows if specific columns have certain values.
I tried different ways of doing it and I end up with a lot of headache.
I used other people codes but they were just to hard to understand what’s going on.
So I came up with this solution of solving the problem: I used Auto filter to select specific values and just deleted the rows.
Now I’m wondering why I don’t see other people use this solution?
Is there a major flaws in solving the problem this way?
If somebody has better and easier to understand solution will be glad to try using it.

What I did here is auto filter for column D and value 99 and deleted it and then column D and value blank and deleted it and finally column F and value 0 and deleted it.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/16/2008 by Preferred User
'
Cells.Select
Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="99"
Rows("2:65536").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=6, Criteria1:="0"
Rows("2:65536").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=4, Criteria1:="="
Rows("2:65536").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.AutoFilter Field:=4
'
End Sub

Hello,

I am trying to create a macro that would be able to delete an entire row of data if a value in column A is equal to a range on a different sheet. Any assistance would be greatly appreciated.

On Sheet “ISQ Raw Data”

If any value in column A (starting in row 2) is equal to a value on Sheet “Old Response IDs” Range A:A(all of column A)

Then Delete that entire row in Sheet “ISQ Raw Data”

Best,

I found the following code on online:

"Delete rows with specific value in Column A and on same row specific value in column B "

   Sub Delete_rows_based_on_ColA_ColB()
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
      Dim cell As Range, rng As Range, i As Long
      Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
      For i = rng.Count To 1 Step -1
         If LCase(rng(i).Value) = "standard" _
              And LCase(rng(i).Offset(0, 1).Value) = "card" _
                Then rng(i).EntireRow.Delete
      Next i
      Application.Calculation = xlCalculationAutomatic
      Application.ScreenUpdating = True
    End Sub

I need help recoding it (if possible) so that it looks at all rows and if Column F, G, & H are <=0 then the row gets deleted, but all three columns (F, G, & H) MUST be <=0

As a side request, I cant figure out in the original code where you would enter the "specific value" to look for in A & B.

Hi All,

I hope I'm posting in the right section. I have a problem that I think is quite difficult, but maybe not. I was going to attach the spreadsheet I was working with.

Need to find if duplicate values exist in a Master sheet, compare in New generated sheet and then display the newly added datas in output sheet.

What I want to do, is search for duplicates by comparing master and new sheet by matching the columns datas. Once that is complete, it needs to delete the row of duplicate entries and update the new entries in output sheet.

I have attached the example file for the reference. Which the output should look like.

Hi there-

I want to delete rows if any given cell in A column do not equal values A B M or O

This sounds so simple, can someone remind me how to do this---I don't think I even need to use objects...

Sub DeleteExcept()

Dim LastRow As Long, r As Long

Dim objNAME As Ojbect

LastRow = Range("A" & Rows.Count).End(xlUp).Row

For r = LastRow To 2 Step -1
If Not objNAME.Value = "C" Or _
objNAME.Value = "B" Or _
objNAME.Value = "M" Or _
objNAME.Value = "O" Then
objNAME.Cells.EntireRow.Delete Shift:=xlUp

End If

Hi guys,

I am finishing up a custom user form for an excel macro and was looking for a function you can send a value to it and it will return a true or false if the value exist in a specific column. (it needs to check all records on a specific column without specifying the length it has)

Ex:

Dim Check as Boolean
Dim Value as String
Check = LookItUp (Value)
Tried a few function but they did not work properly. Any help would be much appreciated!

Thanks!

hello,
I do not want to use VLOOKUP. Is there another way to check if my value
exist in a column and if it does then it should just write YES.
Thank you for your help!
DJ Steffo

I need to create a macro to delete a row if the value is less than a number
that I specific

I need to create a macro to delete a row if the value is less than a number
that I specific

Hi All,

I'd like to delete the rows in column K if the value is between -10,000 or +10,000. I found some code from en earlier post that I've been trying to work with, but can't seem to quite get it.

Here is the code:
Dim Rng As Range
Dim calcmode As Long
    
    With Application
         calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
  
    With ActiveSheet
        .AutoFilterMode = False
        
        .Range("K2:k729" & .Rows.Count).AutoFilter Field:=1, Criteria1:=">=(10000)",
Criteria2:="<=10000"
        With .AutoFilter.Range
            On Error Resume Next
            Set Rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                      .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not Rng Is Nothing Then Rng.EntireRow.Delete
        End With
      
        .AutoFilterMode = False
    End With
    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With
Thanks in advance for the help.

Cheers

Hi!

I have a report in which I need to delete the entire row for each cell in Column A that has the name "Defacto" in a certain location in that cell. I am trying to use VBA code in conjuction with the "MID" function [i.e., Mid(Cell.Value, 8, 7) = "Defacto"]. This is the code I came up with (but, obviously, it doesn't work):

Sub DeleteRowOnCondition()
Range("A2").Select
Range(ActiveCell, ActiveCell.End(xlDown)).Select
For Each Cell In Selection
If Mid(Cell.Value, 8 , 7) = "Defacto" Then Rows.Delete
Next Cell
End Sub

As well, there is another worksheet in the same report in which I need to delete all the rows that do NOT meet that condition (while retaining the ones that do).

Any help you can lend will be much appreciated!

Hi,

I have a long list that has all the services from our stores. I´m using the formula below to count how many services does each store have that are over 20 days old.

=SUMPRODUCT((G!$A$2:$A$6000=$B4)*(NOT(G!$I$2:$I$6000="Valmis/Odottaa nouto"))*(NOT(G!$I$2:$I$6000="Valmis, toimitettu"))*(NOT(G!$I$2:$I$6000="Finished/WaitingDeli"))*(Päivämääärät!$C$1-G!$C$2:$C$6000>Päivämääärät!$I$3))

Now i´m wondering is it possible to make a macro/vba code that would delete all the cells/rows in the range that do not match the criteria of my formula?

With one exception in sheet G cell A2--> has the number of the store. There are over 40 stores so each store has a number 10, 20, 30, etc...
This is the part of the formula that determines witch store it is counting
(G!$A$2:$A$6000=$B4) B4=10 in this case and B5 would be 20 and so on... By modifying that one part i can count old services for all of our stores.

Now I want the macro to delete all services from the list that are not over 20 days old and belong to the store 10 (B4). If it would delete everything that dont match that formulas criteria i could not get a list of old services for our other stores.

What i´m trying to accomplish is get a list of the services that are over 20 days old. I´m now picking them from the list by hand and that takes a really long time. Because of the number of the stores and the size of the range.

I tryed using autofilter/custom filter but i did not get the results that i need.

Is this possible?

Hi everyone,
Any suggestions on how to delete rows by VBA?
The condition is, If the value on Cell (From D7 to last record) is equal to "#VALUE!"
It will delete the entire row.
Appreciate your help once more. Thank you in advance.

Andrew

Hi guys, I believe this will be fairly simple, but I am a noob at VBA so I don't know how to do it.

I am using Ron de Bruin's code to delete a row if a certain criteria is met. The crucial part is:

Firstrow =
.UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1

            'We check the values in the A column in this example
            With .Cells(Lrow, "A")

                If Not IsError(.Value) Then

                    If .Value = "ron" Then .EntireRow.Delete
                    'This will delete each row with the Value "ron"
                    'in Column A, case sensitive.

                End If

            End With

        Next Lrow
How can I modify it so that it will delete the row only if the value in column "A" AND the value in column "B" are "ron"?

Thanks in advance!!

I need to delete the entire row IF the value in Column A is NOT a date.

I need to check this for about 10K rows.
So I can use LR.

Thanks.

Hello,

I need help with a macro that will delete certain rows in a spreadsheet.
The rows that need to be deleted are never in the same place, or the same
number of rows.

The row that need to be deleted will have a value 0 in two columns G & H.
Only if the value in the columns G & H is zero (0), the entire row should be
deleted. If either of the columns has value other than 0 it should remain in
the sheet.

Please suggest me on this.

I need a code that will go thru column C and delete the whole row if cell value is 78315,
Also it needs to shift the the rows up so there is no empty rows wheneverything is done done
I used some codes but they take forever to go thru whole sheet, so please the fast and simple code would be appreciated.
Thanks

Hi all, sorry for asking this question, but I am new and still learning VBA,
but would like a little help if possible. What I would like to have happen,
is if a cell in column C is blank, or contains a set of numbers like
'1540/06', then it would delete that row. The last two numbers is the day of
the month, so if the last two numbers were not the current day of month, then
it would delete that row. Example below; lets say todays date is the 5th, I
would like it to delete rows 1 & 3, because the date is incorrect in Col C in
row 1, and blank cell in Col C in row 3.

A B C
363 MEM 1450/06
616 BOS 2356/05
225 MEM
455 LAX 1767/05
224 LAX 0540/05

Thanks for any help you could give.

Steve D.

Hi all,

I've got a bit of code I need some help with. I am trying to delete rows that have any data in them in a specific column in an excel sheet. The code I have so far:

Sub deleteExtras()

Dim j, LastRow As Integer
LastRow = ActiveSheet.UsedRange.Rows.Count
For j = 2 To LastRow
If Range("A" & j).Value <> "" Then
Range("A" & j).EntireRow.Delete
End If
Next

End Sub I feel like this should work but when I run it, all I get is a lot of seemingly random deleting and modifications to the file.

Any help would be appreciated. Thanks in advance.

Hi,

I have a following code and I need help fixing it.

First of all I have 5 sheets and I'm wondering if it can be adjusted so that the first part of formula goes thru 5 sheets at once rather them me changing the sheet a repeating the formula again.

in the 2nd part of the formula i'm wondering how it can be adjusted so that It can delete other valuer rather then just value "50"
for example values like" 50, 30, 25".

I also need a 3rd part which would be formula to go thru column c and delete rows if they have various values.

I would appreciate if someone can help me out with this and most important is to keep the formula simple so I can understand what's going on,
or maybe explain how it was done.

Thank you very much.

Sub Delete()

'

 '1st part formula-----------------------------

'SHEET ARP

 

    Sheets("ARP").Select

   

    

    Range("A1").Select

   

Dim x As Long

Dim last_Row As Long

 

last_Row = Range("A80536").End(xlUp).Row

 

For x = last_Row To 1 Step -1

    If Range("D" & x).Value < CDate("01/01/2011") Then

        Range("D" & x).EntireRow.Delete

    End If

Next x

 

'2nd part of formula-------------------------------
 

Sheets("ARP").Select

Range("A1").Select

 

 Dim Y As Long

Dim last_RowB As Long

 

last_RowB = Range("A80536").End(xlUp).Row

 

For Y = last_RowB To 1 Step -1

    If Range("A" & Y).Value = “50” Then

        Range("A" & Y).EntireRow.Delete

       

    End If

Next Y

 

End Sub


I have a spreadsheet that I need to hide/show rows if a certain value exists in column D or column I... I need to show rows 59-69 if column D or column I contains the words failed or limited.... I need to hide rows 59-69 if columns D and column I do not contain the words failed or limited...

I also need this to work on its own, not a user button to run a macro.... both column D and I are validation drop down lists with the words "failed" and "limited" as choices.


No luck finding an answer? You could always try Google.