problem with a macro autofilter to delet rows

  • Thread starter Thread starter 1234
  • Start date Start date
1

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.
 
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????).
 
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
 
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????
 
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
 
If .Cells.SpecialCells(xlCellTypeVisible) = 1 Then
should be:

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