Delete row if bellow/lower/under cells have value matches [VBA]

[tocy777]

So I have this table, my goal is to remove the ones that are bold. The logic is to remove the rows which contain additional FALSE value, so I would end up with a FALSE, TRUE, FALSE, TRUE row.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]False[/TD]
[/TR]
[TR]
[TD]True[/TD]
[/TR]
[TR]
[TD]False[/TD]
[/TR]
[TR]
[TD]False[/TD]
[/TR]
[TR]
[TD]False[/TD]
[/TR]
[TR]
[TD]True[/TD]
[/TR]
</tbody>[/TABLE]


Why?

I am scraping a site which has this annoying little html gimmick where the table is actually in a list format so when I paste it into Excel it looks like this, in the second column I am using the formula ISNUMBER:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Apple[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]2.07[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Microsoft[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]IT[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]1.42[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

I want the bold lines to be gone, so I would end up with a company name and a value.

I have been trying but couldn't come up with a real solution, I am also open to a VBA approach!

Thanks in advance!

 

[Joe4]

Assuming that your data starts on row 2, enter this formula in C3 and copy down:

=IF(B2=FALSE,C1+1,0)

Then you can use Filters and filter out any values greater than 1.

Note: If your data starts in row 1, just enter a 1 if row 1 is FALSE, else enter a zero.

If you want to actually delete instead of filtering the data, you can used Advanced Filters to filter to a new location, or use VBA (that's is the approach I would probably take).

 

[Joe4]

Here is a VBA solution. You don't need the helper column B to use this:

Sub MyDeleteRows()

    Dim lastRow As Long
    Dim myRow As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows
    For myRow = lastRow To 2 Step -1
        If Not (IsNumeric(Cells(myRow, "A"))) And Not (IsNumeric(Cells(myRow - 1, "A"))) Then
            Rows(myRow).Delete
        End If
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

 

[tocy777]

I am beyond grateful! You saved me some very precious time and brain cells, I hope I can repay you some day in some shape or form!

 

[Joe4]

You are welcome!

hope I can repay you some day in some shape or form!

Just "pay it forward", in any way you can!

 

[Rick Rothstein]

Using a completely different method, I believe this macro will also do what you asked for in case you might want to consider it...

[table="width: 500"]
[tr]
	[td]Sub KeepNumbersAndFirstNonNumbers()
  Dim Ar As Range
  Application.ScreenUpdating = False
  For Each Ar In Columns("A").SpecialCells(xlConstants, xlTextValues).Areas
    If Ar.Count > 1 Then Ar.Offset(1).Resize(Ar.Count - 1).EntireRow.Delete
  Next
  Application.ScreenUpdating = True
End Sub
[/td]
[/tr]
[/table]