Excel VBA to delete blank rows within a range

Question

I have worksheet that have data starting at A84, extending to column X. I use this VBA to select the entire range of data.

Dim Lastrow As Integer
Lastrow = Range("A:Z").Find("*", , , , xlByRows, xlPrevious).Row

Range("A84:X" & Lastrow).Select

Within that selected range, I need it to detect which rows are blank from columns A to Z and delete them. If there's data after column Z, the row should be deleted because I'm considering it blank.

Loop backwards through the range rows: For i = Lastrow to 84 step -1 then: If Application.WorkSheetFormula.CountA(Range(Cells(i,1),Cells(i,26)))=0 Then Rows(i).Delete — Scott Craner
– Scott Craner, Commented Oct 6, 2016 at 13:52
@ScottCraner I'm sorry. I might be doing something wrong, but I get a syntax error when I run that. — Robby
– Robby, Commented Oct 6, 2016 at 14:00
It just says "Compile error: Syntax error" for If Application.WorkSheetFormula.CountA(Range(Cells(i,1),Cells(i??,26)))=0 Then Rows(i).Delete — Robby
– Robby, Commented Oct 6, 2016 at 14:03

Scott Craner · Accepted Answer · 2016-10-06 14:16:28Z

2

The comments sometimes adds characters. Here is the code:

Dim Lastrow As Integer
Lastrow = Range("A:Z").Find("*", , , , xlByRows, xlPrevious).Row


For i = Lastrow To 84 Step -1
    If Application.CountA(Range(Cells(i, 1), Cells(i, 26))) = 0 Then Rows(i).Delete
Next i

edited Oct 6, 2016 at 14:16

answered Oct 6, 2016 at 14:07

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Robby Over a year ago

Now I'm getting this error: Run-time error '438': Object doesn't support this property or method. And this is what is highlighted: If Application.WorkSheetFormula.CountA(Range(Cells(i, 1), Cells(i, 26))) = 0 Then

Robby Over a year ago

Wait. Should it be Application.WorkSheetFunction instead of ApplicationWorkSheetFormula? I made that change, and it works.

Scott Craner Over a year ago

@Robby that's what I get for just typing it in the answer. Yes, or like the edit above. Remove the WorkSheetFunction part completely.

Scott Craner Over a year ago

@Robby remember to mark as correct by clicking the check mark by the answer.

Robby Over a year ago

After testing this some more, I ran into an issue when using Excel 2010: If I had the blank row in view when I run this code, it always deleted the row. But if the blank row wasn't in view when the code was ran, sometimes it didn't delete the row. Instead it just removed the row number from the grey cell, which I thought was weird. I solved this bug by including the WorkSheetFunction bit in that second to last line.

|

Collectives™ on Stack Overflow

Excel VBA to delete blank rows within a range

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related