problem with a macro autofilter to delet rows

[1234]

Hi,

I have this sheet with some if formula applied to it. I want all rows
that have false as a value as a result of the if formula to delete. If
I try a macro with the False value it doesn´t work because it doesn´t
recognize the value false. If I try doing a macro that searches by
that term it can´t find it without converting them to values first and
I thpught of recording a macro with the autofilter, but what a
surprise that when I run it, it doesn´t apply the autofilter. What am
I doing wrong or how can I delete all those rows with the false value.

Thank you so much for your knowledge.

 

[Dave Peterson]

This worked ok for me.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim VisRng As Range

Set wks = ActiveSheet

With wks
'remove any existing filter
.AutoFilterMode = False
.Columns(1).AutoFilter Field:=1, Criteria1:="FALSE"

With .AutoFilter.Range.Columns(1)
If .Cells.SpecialCells(xlCellTypeVisible) = 1 Then
MsgBox "only header visible"
Else
'avoid the header
Set VisRng = .Resize(.Cells.Count - 1).Offset(1, 0)
VisRng.EntireRow.Delete
End If
End With
.AutoFilterMode = False 'remove the filter
End With
End Sub


If it doesn't work for you, post the code you tried--and share what those
"false" values are--text or boolean (check for leading/trailing spaces????).

 

[1234]

Hi, Dave

Thank you so much for your answer. It doesn´t work. Look I recorded
the macro starting on cell A33 which is where the data starts. All the
cells which show False are as a result of

Sub Macro1()
Selection.AutoFilter
ActiveSheet.Range("$A$33:$E$56").AutoFilter Field:=1,
Criteria1:="FALSE"
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.EntireRow.Delete
Range("A38").Select
Selection.AutoFilter
End Sub

 

[Dave Peterson]

The cells that show false are not the result of the autofilter. They could be
the result of you typing them in or a formula that evaluates to false. But you
didn't explain what you typed in (text or boolean) or the formula that evaluated
to false.

This worked for me when I had a formula like:
=b54>0
This returns a TRUE or FALSE (boolean).

It also worked if I typed in 'False

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim VisRng As Range
Dim myRng As Range

Set wks = ActiveSheet

With wks
'header in A33, right?
Set myRng = .Range("a33:a56") 'just a single column
'or if you wanted to start in A33 and go to the last used cell
'in column A:

Set myRng = .Range("A33", .Cells(.Rows.Count, "A").End(xlUp))

'remove any existing filter
.AutoFilterMode = False
myRng.AutoFilter Field:=1, Criteria1:="FALSE"

With .AutoFilter.Range.Columns(1)
If .Cells.SpecialCells(xlCellTypeVisible) = 1 Then
MsgBox "only header visible"
Else
'avoid the header
Set VisRng = .Resize(.Cells.Count - 1).Offset(1, 0)
VisRng.EntireRow.Delete
End If
End With
.AutoFilterMode = False 'remove the filter
End With
End Sub

If it doesn't work for you, then I don't think you have False in the
cells--maybe a leading/trailing space????

 

[Herbert Seidenberg]

Excel 2007 Table (only)
Remove "False" rows.
Macro
http://c0444202.cdn.cloudfiles.rackspacecloud.com/12_20_09.xlsm

 

[1234]

Hi, Dave

Thank you so much for your effort and help. The macro works great
until it gets to this line:

If .Cells.SpecialCells(xlCellTypeVisible) = 1 Then
MsgBox "only header visible"

Here it throws error 13 saying that types don´t match.

What can I do now?

Thanks

 

[Dave Peterson]

If .Cells.SpecialCells(xlCellTypeVisible) = 1 Then
should be:

If .Cells.SpecialCells(xlCellTypeVisible).cells.count = 1 Then