Moving Text upwards into empty rows

**Jiptastic** · 12-13-2012, 07:31 PM

This is the best that I can do for getting rid of blank rows. It's a little bit slow, and alternatively I would like to move all the text upward into any empty spaces without deleting rows. Any ideas?

Range("A2401").Select
For i = 1 To 2400
ActiveCell.Offset(-1, 0).Select
If ActiveCell.Value = Empty Then
ActiveCell.EntireRow.Delete
End If
Next i

**oeldere** · 12-13-2012, 07:39 PM

why not delete het empty (blanc) rows?

Sub DeleteBlankRows()
Dim r As Long
For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(r, 1) = "" Then Rows(r).Delete
Next r
End Sub

**JieJenn** · 12-13-2012, 07:48 PM

To be honest, you just need one line

Range("A1:A2400").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

**Jiptastic** · 12-14-2012, 12:41 AM

aHAA. JieJenn - I had no idea it was that easy. Thanks.

**oeldere** · 12-14-2012, 03:22 AM

@JieJenn

In your code (#3) you need to hard code the range.

In my found code (#2) it automatic counts all rows.

I think (#2) is easier for common use.

By the way, I think one line is not always better than more lines.

If it is easier to read to code if it is more lines, i prefer the last one.

In that case it is easier to change (to my opinion)