IF OR with LEN

[Babio1964]

Hello,
This on has me baffled and I tried everything I could think of. Longstory short, I'm working with an old windows program that extracts data into anexcel worksheet. There are a ton of rows,but many are useless (like headers, totals, other stuff) Anyway, I did discover that the data I needhad a consistent length of 188 characters, so I wrote a little sub that findsall the data not 188 characters and removes all those rows. This worked ideally, until I later discoveredthere were a few outliers. Fortunately,these outliers are also consistently 123 characters. Currently, this is the code IÂ’m using for the first one isbelow.

Range("A1").Select

For Counter = 1 ToLastRow

IfLen(ActiveCell.Value) <> 188 Then
ActiveCell.EntireRow.Delete ' Delete Row

Else
ActiveCell.Offset(1,0).Select ' Move down a row

End If
Next Counter

My problem is that when I try to incorporate anadditional condition (also keeping the 123 characters) everything is deleted. In short, I need to keep all the LEN = 188 aswell as the LEN = 123 rows, but remove everything else. I tried: If Len(ActiveCell.Value)<> 188 OR Len(ActiveCell.Value) <> 123 Then.. to no avail

I’d covet any help

Thank you!

 

[FormR]

Hi, you need to use AND not OR..

Also, when deleting rows you should loop from the bottom upwards, for example:

For Counter = LastRow To 1 Step -1


    If Len(Cells(Counter, "A").Value) <> 188 And Len(Cells(Counter, "A").Value) <> 123 Then
        Cells(Counter, "A").EntireRow.Delete ' Delete Row
    End If


Next Counter

 

[Babio1964]

thank you! worked like a charm. As I reread it with the AND statement, it makes perfect sense