Delete the Blank lines

[reachbds]

Hi,

I have a dump of xls files in a folder, all these files have blank lines in them , can you give me some idea on how to write a macro which will delete the blank lines from all the files in a particular folder.

Thx,Bds

[JonathanMoore]

I'm not sure how to delete blanks from all files within a folder... but a quick work-around option might be to place a simple macro in the "Workbook_Open" function of each individual sheet...

Private Sub Workbook_Open()
Range("A1").Select
For x = 1 To 1000 (OR HOWEVER MANY ROWS YOU HAVE)
    If ActiveCell.Value <> "" Then
       ActiveCell.Offset(1, 0).Select
    Else
        Selection.EntireRow.Delete
    End If
Next x
End Sub

[tigeravatar]

reachbds,

Give the following a try. It will prompt you to select a folder, and then remove blank rows from each worksheet in each .xls file in that folder.

Sub tgr()
    
    Dim strFldrPath As String
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Show
        On Error Resume Next: strFldrPath = .SelectedItems(1)
    End With
    If strFldrPath = vbNullString Then Exit Sub
    
    Dim CurrentFile As String: CurrentFile = Dir(strFldrPath & "\" & "*.xls*")
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim cIndex As Long
    
    Application.ScreenUpdating = False
    While CurrentFile <> vbNullString
        Set wb = Workbooks.Open(strFldrPath & "\" & CurrentFile)
        For Each ws In wb.Sheets
            With ws.UsedRange
                For cIndex = 1 To .Columns.Count
                    .AutoFilter cIndex, "="
                Next cIndex
                .Offset(1).EntireRow.Delete xlShiftUp
                .AutoFilter
            End With
        Next ws
        wb.Close True
        CurrentFile = Dir
    Wend
    Application.ScreenUpdating = True
    
End Sub

Hope that helps,
~tigeravatar