Results 1 to 5 of 5

Thread: How to delete each empty row in a spreadsheet

  1. #1

    How to delete each empty row in a spreadsheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    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?
    John

  3. #3

    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
    Attached Files Attached Files

  4. #4
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    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
    John

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    751
    Articles
    0
    Excel Version
    Excel 2010 64bit

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •