Delete Blank Rows

[MSingh]

Hi,

I'm trying to delete blank rows:
Scenario: Data copied from sheet1 & PasteSpecial to sheet 2.
Data in Sheet2 is in Range A15:I213. The cells in this range is formatted, borders are outlined.

I have tried several other code but it does not delete the blank rows.

Here is an example code adapted:

Sub DeleteEmptyRows() 'Code Not my own
Dim LastRow As Long
Dim r As Long
Dim Counter As Long
Application.ScreenUpdating = False
LastRow = ActiveSheet.Range("A15:A213").Rows.Count + _
ActiveSheet.Range("A15:A213").Rows(1).Row - 1
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 Then
Rows(r).Delete
Counter = Counter + 1
End If
Next r
Application.ScreenUpdating = True
MsgBox Counter & " Empty rows were deleted."
End Sub


Please advise

Thanks again
Mohamed

[HansV]

If a formula results in an empty string, the value of that cell is "", even after Paste Special > Values. This does NOT count as a blank.
You could clear such cells before deleting the empty rows:

Code: Select all

Sub DeleteEmptyRows() 'Code Not my own
  Dim rng As Range
  Dim LastRow As Long
  Dim r As Long
  Dim Counter As Long
  Application.ScreenUpdating = False
  LastRow = 213
  ' Clear seemingly blank cells
  For Each rng In Range("1:" & LastRow).SpecialCells(xlCellTypeConstants)
    If Trim(rng.Value) = "" Then
      rng.ClearContents
    End If
  Next rng
  ' Delete empty rows
  For r = LastRow To 1 Step -1
    If Application.WorksheetFunction.CountA(Rows(r)) = 0 Then
      Rows(r).Delete
      Counter = Counter + 1
    End If
  Next r
  Application.ScreenUpdating = True
  MsgBox Counter & " Empty rows were deleted."
End Sub

(Your code for LastRow seemed an overly elaborate way to calculate 213, so I omitted it)

[MSingh]

Hi Hans,

Thanks again. Works perfectly.

Kind Regards
Mohamed

[kpark91]

Or you can just use this code I believe.
Not very sure but..

Code: Select all

Range("A15:A213").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

[HansV]

The line

Range("A15:A213").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

deletes rows that have a blank in column A. Mohamed wants to delete rows in which ALL cells are blank.

[kpark91]

Hmm.... Is there a one-liner code for this then?

Would offset work?

[HansV]

I don't think you can do this with a one-liner.