Fails to delete empty rows

[Len]

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(xlCellTypeLastCell).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

 

[Dave Peterson]

I think you'll have to find out what's in those cells that look blank, but
aren't.

They could be space characters, HTML non-breaking space characters, or something
else.

You can use Chip Pearson's Cell View addin to determine those invisible
characters.
http://www.cpearson.com/excel/CellView.aspx

After you've found those characters, you could add a bit to fix them
(edit|Replace matching entire cell???).

Another thing to consider:

If you've manipulated the data using formulas that evaluated to "" like:

=if(a1>5,A1,"")

Then those cells with the formulas aren't empty.

In fact, if you've converted those formulas to values, the cells are still not
empty. But there are ways to overcome this, too.

 

[JLGWhiz]

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.

 

[ryguy7272]

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

 

[Len]

Dear all friends,

Thanks for your prompt reply

Thanks for your advice on cells that contains space characters or
something else

Yes, I managed to eliminate those cells that contain excess space by
using "Trim Spaces " Add-In Excel ( Big thanks to AbleBits.Com) in
about 20,000 rows which took about 5 minutes

http://www.ablebits.com/excel-trim-spaces/index.php

Then, I use the excel codes to delete blanks rows and finally it
really works !!


Cheers,
Len