delete empty rows
 

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

delete empty rows
 

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


"geebee" wrote:

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

delete empty rows
 

On 28 Nov, 21:49, sebastienm
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 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



"geebee" wrote:
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- 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