Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

Excel macro delete rows if a cell equals a certain logical value?

I want a macro to delete all rows in which a logical value of FALSE is in the cell in Column A. I want it to begin searching with row 11 and move down until it finds no more data.

Note that FALSE is not a text value but is a logical expression of an AND statement.

8 Answers

Relevance
  • 1 decade ago
    Favorite Answer

    Here is a short macro that will do as you ask. It will work for Excel 2003, and prior, having 65536 rows and Excel 2007 having 1,048,576 rows.

    Sub DeleteRows()

    Dim i, LastRow

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

    For i = LastRow To 11 Step -1

    If Cells(i, "A").Value = False Then

    Cells(i, "A").EntireRow.Delete

    End If

    Next

    End Sub

  • 1 decade ago

    I like Garbo's answer a lot. His is definitely the best structured of the ones I see. But I like to do things a little differently. First I like to turn the screen updating off until the end so that the macro runs faster. Then I like to use the intersection of the Used Range and that column. If you use the xlUP it will take you to the first non-empty cell in that column, which for your needs will probably work. But sometimes you'll have data where Column A goes to Row 350, but the data in Column D goes to row 368. If you used the xlUP, you'd have missed deleting those last 18 rows or so if you wanted to delete them because the macro would have gone right up to A350. The way I do it goes to the very last row with data. So if you wanted to get rid of that row you could if you wanted, even though it was blank. It's not going to matter in your case because you are looking for the FALSE statement, but if you were looking for FALSE or a blank cell then it would make a ton of difference.

    Dim MyRange As Range

    Dim x As Long

    Dim MyLastRow As Long

    Application.ScreenUpdating = False

    Set MyRange = Intersect(ActiveSheet.UsedRange, Range("A:A"))

    MyLastRow = MyRange.Row + MyRange.Rows.Count - 1

    For x = MyLastRow To 11 Step -1

    If Cells(x, 1).Value = False Then

    Cells(x, 1).EntireRow.Delete

    End If

    Next x

    Application.ScreenUpdating = True

    Here's deleting for a for False or a blank cell:

    Dim MyRange As Range

    Dim x As Long

    Dim MyLastRow As Long

    Application.ScreenUpdating = False

    Set MyRange = Intersect(ActiveSheet.UsedRange, Range("A:A"))

    MyLastRow = MyRange.Row + MyRange.Rows.Count - 1

    For x = MyLastRow To 11 Step -1

    If Cells(x, 1).Value = False Or Cells(x, 1).Value = "" Then

    Cells(x, 1).EntireRow.Delete

    End If

    Next x

    Application.ScreenUpdating = True

    You also don't need to specify Column A by putting Cells(x, "A") in. Column A is the first Column so you can just use the number 1. And I like to make sure that for most variables I declare that I state what type they are. It saves space and time. The only time you don't want to do declare the type is if you need to switch the type from one type to another. Then you leave it as the Variant type, which is the same as not declaring it.

    Also the following two lines are equivalent. The Cells one just is a little shorter to type.

    Range("A" & Rows.Count).End(xlUp)

    Cells(Rows.Count, 1).End(xlUp)

  • Anonymous
    6 years ago

    This Site Might Help You.

    RE:

    Excel macro delete rows if a cell equals a certain logical value?

    I want a macro to delete all rows in which a logical value of FALSE is in the cell in Column A. I want it to begin searching with row 11 and move down until it finds no more data.

    Note that FALSE is not a text value but is a logical expression of an AND statement.

    Source(s): excel macro delete rows cell equals logical value: https://biturl.im/h41oK
  • 4 years ago

    1

  • How do you think about the answers? You can sign in to vote the answer.
  • 1 decade ago

    The code is here:

    Option Explicit

    Sub subDeleteFalse()

    Dim objWrksheet As Worksheet

    Dim lngRowNo As Long

    Dim lngRowCount As Long

    On Error GoTo errorhandler

    Set objWrksheet = ActiveSheet

    lngRowCount = objWrksheet.Cells(11, 1).CurrentRegion.Rows.Count

    If lngRowCount = 0 Then Exit Sub

    lngRowNo = 11

    Do

    If objWrksheet.Cells(lngRowNo, 1).Value = False And objWrksheet.Cells(lngRowNo, 1).HasFormula = True Then

    objWrksheet.Rows(lngRowNo).Delete

    Else

    lngRowNo = lngRowNo + 1

    End If

    lngRowCount = lngRowCount - 1

    Loop Until lngRowCount <= 0

    Exit Sub

    errorhandler:

    End Sub

    Counting of the number of rows using the "CurrentRegion" will not work correctly if there is an empty row in your table. You will need to put something into each empty row. "CurrentRegion" works like pressing Ctrl+* (ctrl+asterisk) in your table.

    I hope this helps.

  • Anonymous
    7 years ago

    The best reverse cell phone lookup service that I have tried is http://reversephone.toptips.org/

    From using a reverse cell phone lookup you will be able to get the callers name and address just

    by typing the phone numbers they will tell you the details related to the number.

    The reverse cell phone lookup service has your details available they will give you the cell phones location. For a full report including a name and address and other interesting informations you will have to pay.

    Enter the number in the system and they will tell you a lot of informations about the phone number and the person who owns it.

    If they have extra details you will have to pay for the report.

    Free reverse phone lookup sites generally provide the most basic of information such as name and it works only for landlines. To get further information, money will have to be paid. The free searches do not provide much more than what can be found through the phone book.

    This service is great, I used it a lot f time with great results: I always obtained what I was searching for.

    I definitely reccomend this service.

  • 6 years ago

    I need a macro to delete all rows of multiple spreadsheets of the same workbook that show "#N/A" as the result of a V look-up. Using Excel 14....... of Office Pro 2010.

  • 1 decade ago

    This macro may help you:

    Sub RemoveFalses()

    dim iRow as long

    dim iLoop as long

    irow = range("a65000").end(xlup).row

    for iloop = irow to 11 step - 1

    if cells(iloop,1).value = false then

    cells(iloop,1).entirerow.delete

    end if

    next iloop

    end sub

    This code identifies the exact number of rows you have and start removing rows from end to start.

Still have questions? Get your answers by asking now.