How to delete each empty row in a spreadsheet

Michael1974

New member
Joined
Mar 31, 2015
Messages
49
Reaction score
0
Points
0
Hello,

I have the attached spreadsheet. I am looking for a macro that will delete each empty row. In order word, a code that will identify any empty row and just delete it. Can someone help with this?

Thanks

Michael
 

Attachments

  • Book3.xls
    55 KB · Views: 10
Hi Michael

Your Sample File does not contain any "empty rows". Please define "empty row" for us...is it if a Cell in Column A is blank?
 
Delete Empty Rows

Hello,

Please see in the attached a spreadhseet that contains Empty Rows and some "----". I am looking for a macro that will help me to eliminate all the empty rows and the rows containing "---". I would be very grateful if someone can help.

Thanks in advance

Michael
 

Attachments

  • How to delete each Empty Row.xls
    168.5 KB · Views: 14
Hi Michael

Try this
Code:
Option Explicit
Sub DeleteEmptyRows()
   Dim ws           As Worksheet
   Dim DeleteRange  As Range
   Dim rCount As Long, r As Long
   Dim LR           As Long
   Dim LC           As Long
   Set ws = ActiveSheet
   Application.ScreenUpdating = False
   With ws
      LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious).Row
      LC = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
                       SearchDirection:=xlPrevious).Column
      Set DeleteRange = .Range(.Cells(3, 1), .Cells(LR, LC))
      If DeleteRange Is Nothing Then Exit Sub
      If DeleteRange.Areas.Count > 1 Then Exit Sub
      With DeleteRange
         rCount = .Rows.Count
         For r = rCount To 1 Step -1
            If Application.CountA(.Rows(r)) = 0 Then
               .Rows(r).EntireRow.Delete
            End If
         Next r
      End With
      .Columns("A:A").AutoFilter Field:=1, Criteria1:="-------"
      .Range(.Cells(3, 1), .Cells(LR, 1)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
      .AutoFilterMode = False
   End With
   Application.ScreenUpdating = True
End Sub
 
Back
Top