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.

 

[RoryA]

In what way does it not work? The code is the same in 2010.

 

[TTTon1]

It doesnt work in the sense that it does nothing meaning it doesnt delete the empty rows within the set range. There are no errors.

 

[RoryA]

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.

 

[Joe4]

I have Excel 2010 and had to make one minor modification to your code to get it to work (see the word in red):

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).[B][COLOR=#ff0000]EntireRow.[/COLOR][/B]Delete
  Next
  End Sub

But I guess it begs the question, are you checking for the WHOLE row to be blank, or just the row between columns A and D (your range)?
If the whole row, then what is the point of setting the range variable from columns A:D?

 

[RoryA]

That would delete the entire sheet row, not the entire row of A:D. Not sure which is the desired behaviour, but that hasn't changed between 2003 and 2010.

 

[RoryA]

@TTTon1

Is the range in question a normal range, or a Table?

 

[Joe4]

That would delete the entire sheet row, not the entire row of A:D. Not sure which is the desired behaviour, but that hasn't changed between 2003 and 2010.

Yep, not sure if you saw the amendment to my post (we may have crossed in posting).

I ran some tests where I had empty cells in columns A:D, but data out in further off columns. So it deleting the blanks, shifting the data from the other columns over into columns A:D.
I figured that is not what they probably want, but am not 100% certain as to their data structure and desired goals.
(And unfortunately, I do not have access to Excel 2003 to verify the behavior there).

 

[TTTon1]

Sorry for not beeing clear. I dont want to delete the entire row of the sheet, only the cells within the range if the whole row within the range is blank.

I hope this makes sense.

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.

I tested it by running it for each column and it works but once I add another column it wont.

 

[RoryA]

I tested it by running it for each column and it works but once I add another column it wont.

Can you elaborate on that?