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

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

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)

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.

1

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.

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.

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.

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.