delete empty rows

[geebee]

hi,

i have a sheet 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

 

[sebastienm]

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 ActiveSheet.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

 

[james.billy]

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 ActiveSheet.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>







- Show quoted text -

Hi Geebee,

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

=IF(And(Len(B3)<>3,Count(A33)=0),1,0)

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

Jame