I concur with the others. You're going to have to do some troubleshooting
and see what's REALLY in those cells. You may have hard returns in there.
This is probably a step in the right direction:

Sub Remove_CR_LF()
With Selection
..Replace What:=Chr(39), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(146) & Chr(10), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
..Replace What:=Chr(180), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub

May take a bit more work on your part though...

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JLGWhiz" wrote:

I set up a range of data on a worksheet with several rows being blank. In
some of the rows I had only one cell with data. The code you posted worked
to delete all blank rows, leaving only those with data including those with
only one cell of data.

Make sure the rows are really blank and do not containg formulas that equate
to null string (""). Also, make sure the sheet you want to delete the rows
from is the active sheet.

"Len" wrote in message
...
Hi,

After data exported to excel file which contain about 20,000 rows and
20 columns, certain cells contain no data or value but do not belong
to blank cells ( ie after using GoTo Special/Search for blanks cell )
I tried to delete entire rows that contain no data from the above file
by using the codes in Excel 2007 below ( thanks to VBA Express ) and
the result has no response

Sub DeleteBlankRows()
Dim Rw As Long, RwCnt As Long, Rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error GoTo Exits:

If Selection.Rows.Count 1 Then
Set Rng = Selection
Else
Set Rng = Range(Rows(1),
Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLast Cell).Row()))
End If
RwCnt = 0
For Rw = Rng.Rows.Count To 1 Step -1
If
Application.WorksheetFunction.CountA(Rng.Rows(Rw). EntireRow) = 0 Then
Rng.Rows(Rw).EntireRow.Delete
RwCnt = RwCnt + 1
End If
Next Rw

Exits:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Any help will be much appreciated and thanks in advance as I'm VBA
beginner


Regards
Len




.