Convert "Delete Entire Rows" VBA Code from Excel 2003 to 2010

[TTTon1]

Hello, ive been using the following code to delete entirely empty rows (only if all the cells in 1 row are empty) within a range in excel 2003.

Sub delete1()
  Dim r As Range, rows As Long, i As Long
  Set r = ActiveSheet.Range("A1:D220")
  rows = r.rows.Count
  For i = rows To 1 Step (-1)
    If WorksheetFunction.CountA(r.rows(i)) = 0 Then r.rows(i).Delete
  Next
  End Sub

This code doesnt work in Excel 2010.

Ive read many Threads regarding deleting entire Rows but found nothing that works for what I want to do.

 

[TTTon1]

Can you elaborate on that?

I changed the range to from A:D to A:A , B:B etc and with a single column it would work.

Now ive closed the workbook and opened it again and it wont work with single columns now as well.

The only way that would happen is if there is at least one non-empty cell in each of the rows. That code will delete completely empty rows in 2010.

Youre right, this is what is happening. After manually selecting empty cells and deleting their "content" the code will work.

So the Issue is with exporting the data from a webscraping software since exporting to excel 2003 file will have blanks as blanks while exporting to xlsx will have "invisble" data in the empty cells. I have no idea what data could be in the cells.

Thank you guys for your help!

 

[Joe4]

So the Issue is with exporting the data from a webscraping software since exporting to excel 2003 file will have blanks as blanks while exporting to xlsx will have "invisble" data in the empty cells. I have no idea what data could be in the cells.

Data pulled from the web often has special invisible characters like non-breaking spaces, that are not affected by the TRIM function.
One way to easily check if this is the case is to use the LEN function on these seemingly blank cells. If they look blank, but return a count other than 0, than that is probably what you want going on.

You can see exactly what that character is by using the CODE function, which returns the ASCII code of the character, i.e.

=CODE(LEFT(A1,1))

and then lookup the code on an ASCII table, i.e. https://www.ascii-code.com/