Re: delete empty rows

On 28 Nov, 21:49, sebastienm <sebasti...@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:
Hi,
By <no data> i am not sure whether you mean <empty cell> i.e. as when
clearing a cell or <empty string> . I have included the search for both in
the code bellow, just keep the search sections you are interested in, and
delete the other ones. Also, I assume that by 'length greater than 3' you
mean text values or numeric values with at least 3 characters in it.

Sub TEST()
Dim c As Range, result As Range
Dim firstAddress As String

With Active***.Range("B:B")

''' -----------------------------------------
''' find cells with at least 3 characters
Set c = .Find("????*", LookIn:=xlValues, lookat:=xlWhole) ''' at
least 3 chars: ????*
If Not c Is Nothing Then
firstAddress = c.Address
Do
If result Is Nothing Then
Set result = c
Else
Set result = Application.Union(result, c)
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If

''' -----------------------------------------
''' find blank cells (not empty string cells)
Set c = Nothing
On Error Resume Next
Set c = .SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not c Is Nothing Then
If result Is Nothing Then
Set result = c
Else
Set result = Application.Union(result, c)
End If
End If

''' -----------------------------------------
''' find cells with empty strings
Set c = .Find("", LookIn:=xlValues, lookat:=xlWhole) ''' at least 3
chars: ????*
If Not c Is Nothing Then
firstAddress = c.Address
Do
If result Is Nothing Then
Set result = c
Else
Set result = Application.Union(result, c)
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

''' <<<< delete rows >>>>
If Not result Is Nothing Then
result.EntireRow.Delete
End If

End Sub

--
Regards,
Sébastien
<http://www.ondemandanalysis.com>



"geebee" wrote:
hi,

i have a *** in which there are like 180 or so lines. and some of the
rows do not contain some data. and then there are some rows which contain
data but that have a value in column B in which its length is greater than 3.
how can i write code which deletes rows which contain no data and rows which
contain a value with a length greater than 3 in column B?

thanks in advance,
geebee- Hide quoted text -

- Show quoted text -

Hi Geebee,

If you added another column (assume column E, your data is in columns
A:D) and entered an IF statement to calculate if the two criterias are
met something like:

=IF(And(Len(B3)<>3,Count(A3:D3)=0),1,0)

then just filter for 0's and delete the rows.

Jame
.