how to code to delete empty rows

[hex6007]

Hi guys,

how do i create an erase button in vba in excel that delete all empty rows. please help.

thanks in advance

regards

 

[Geoka]

Try the below code.
Note:If 10 contineous blank cells will stop the execution.

Sub Button1_Click()
mRow = 1
nCnt = 0
While True
    If Trim(Sheet1.Cells(mRow, 1)) = "" Then
        Sheet1.Cells(mRow, 1).Delete
        nCnt = nCnt + 1
        If nCnt = 10 Then 'If 10 contineous blank cells will stop the execution.
            nCnt = 0
            Exit Sub
        End If
    Else
        mRow = mRow + 1
    End If
Wend
End Sub

 

[SkipVought]

Hi,

I'd AutoFilter on the UsedRange. UsedRange can yield uncertian results, but in this case too many rows is OK.

Set the criteria for (blanks).

Delete the visible cells.

Making an assumption based on some number of cells is a crap shoot.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[fumei]

And maybe check out Skip's thread68-1525860, regarding the use of empty rows.

Gerry