VBA to find and select cell

[luvbite38]

Hi

Can someone please help me write a VBA code for the following case?

I have formula in Column C which returns either FALSE or TRUE. I want macro to find All FALSE and select the results.

please help

 

[luvbite38]

I tried the following but I am not sure How to change this to find FLASE in Column C

Sub Macro1()
Last = Cells(Rows.Count, "COLUMN").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "COLUMN").Value) = "FALSE" Then
Cells(i, "A").EntireRow.Delete
End If
Next i
End Sub
Thanks

 

[Rick Rothstein]

I tried the following but I am not sure How to change this to find FLASE in Column C

Sub Macro1()
Last = Cells(Rows.Count, "COLUMN").End(xlUp).Row
For i = Last To 1 Step -1
If (Cells(i, "COLUMN").Value) = "FALSE" Then
Cells(i, "A").EntireRow.Delete
End If
Next i
End Sub
Thanks

Here is one way to do it that should be faster than iteratring every row in Column C...

Sub DeleteFalseRows()
  Dim UnusedCol As Long, LastRow As Long
  UnusedCol = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
              SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Cells(1, UnusedCol).Resize(LastRow) = Evaluate("IF(C1:C" & LastRow & "=FALSE,""X"","""")")
  On Error Resume Next
  Columns(UnusedCol).SpecialCells(xlConstants).EntireRow.Delete
  On Error GoTo 0
End Sub

 

[luvbite38]

Thanks Rick

For some reason it didn't work for me ??

Any other suggestion?

 

[Marcelo Branco]

See if this works (adapting Rick's idea of using SpecialCells method)

Assumes data (False/True) beginning in C1

Sub DelRows()
  Dim rngData As Range, LastRow As Long
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Set rngData = Range("C1:C" & LastRow)
  rngData = Evaluate("IF(ROW(1:" & LastRow & "), (1/" & rngData.Address & ")=1)")
  On Error Resume Next
  rngData.SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub

M.

 

[Marcelo Branco]

IMPORTANT: the code above assumes that the values in column C are Boolean True or False, not text "True" or "False"

M.

 

[luvbite38]

See if this works (adapting Rick's idea of using SpecialCells method)

Assumes data (False/True) beginning in C1

Sub DelRows()
  Dim rngData As Range, LastRow As Long
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Set rngData = Range("C1:C" & LastRow)
  rngData = Evaluate("IF(ROW(1:" & LastRow & "), (1/" & rngData.Address & ")=1)")
  On Error Resume Next
  rngData.SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  On Error GoTo 0
End Sub

M.

Thanks

But it deleted everything from my sheet.... nothing left.... :O save me pls

 

[Marcelo Branco]

Thanks

But it deleted everything from my sheet.... nothing left.... :O save me pls

So I suspect that the values in column C are text values "False" or "True", not logical (Boolean) values True or False.

Could you show us the formula you are using in column C?

M.

 

[luvbite38]

I fixed, but using a totally different approach.

it was not perfect but did the trick for me.

Will post the fix shortly.


Thank you so much for your support all...