Excel VBA to delete blank rows within a range

0
votes

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.

1
vbaexcel
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
@ScottCraner I'm sorry. I might be doing something wrong, but I get a syntax error when I run that. – Robby
What is the error and on which line? – Scott Craner
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

1 Answers

2
votes

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