Macro help - Delete blank rows

[vinayashwitha]

Hi All,

I am using excel 2007.

I am having below code which collates data from different files in the same folder.

Need help in writing code to find any blank rows and delete the same from the data which is collated using the below code.

Thanks in advance.

Public Sub GetData()
Const FILE_PATH As String = "Path of the folder"
Dim This As Workbook
Dim wb As Workbook
Dim wbName As String
Dim NumRows As Long
Dim InsertAt As Long
Set This = ActiveWorkbook
wbName = Dir(FILE_PATH & "*.xlsx*")
If wbName <> "" Then

InsertAt = 1
Do While wbName <> ""

Set wb = Workbooks.Open(FILE_PATH & wbName)
wb.Worksheets("sheet name").UsedRange.Copy This.Worksheets(1).Cells(InsertAt, "A")
InsertAt = InsertAt + wb.Worksheets("sheet name").UsedRange.Rows.Count
wb.Close savechanges:=False
wbName = Dir
Loop
End If

Set wb = Nothing
Set This = Nothing

End Sub

Regards
Vini

 

[p45cal]

Add the bit in red below:

Public Sub GetData()
Const FILE_PATH As String = "Path of the folder"
Dim This As Workbook
Dim wb As Workbook
Dim wbName As String
Dim NumRows As Long
Dim InsertAt As Long
Set This = ActiveWorkbook
wbName = Dir(FILE_PATH & "*.xlsx*")
If wbName <> "" Then

  InsertAt = 1
  Do While wbName <> ""

    Set wb = Workbooks.Open(FILE_PATH & wbName)
    wb.Worksheets("sheet name").UsedRange.Copy This.Worksheets(1).Cells(InsertAt, "A")
    InsertAt = InsertAt + wb.Worksheets("sheet name").UsedRange.Rows.Count
    wb.Close savechanges:=False
    wbName = Dir
  Loop
End If

[COLOR=Red]With This.Worksheets(1)
  For i = InsertAt To 1 Step -1
    If .Cells(i, "[B][COLOR=Blue]A[/COLOR][/B]") = Empty Then .Rows(i).Delete
  Next i
End With[/COLOR]

Set wb = Nothing
Set This = Nothing

This is not especially robust as it relies on just the cells in column A being empty to decide whether the whole line should be deleted.

 

[Boller]

This is not especially robust as it relies on just the cells in column A being empty to decide whether the whole line should be deleted.

In which case could merely delete with one line of code (no loop) via SpecialCells>Blanks.

 

[p45cal]

In which case could merely delete with one line of code (no loop) via SpecialCells>Blanks.

True.

 

[vinayashwitha]

Thanks a lot.

It works. Will get back to you if i need robust method.

Regards
Vini

 

[blody]

Hello ,

I tried some longer code to delete rows as well, addition to your "A cell" being empty condition what if you have 14 coloumns but only one worksheet to delete empty rows. if either of the coloumns has data the row should stay;

The code I tried which is not workin is like this:

Sub deneme()
Dim i As Integer

For i = 1 To 65518
If Cells(i, 1) = "" Then
If Cells(i, 2) = "" Then
If Cells(i, 3) = "" Then
If Cells(i, 4) = "" Then
If Cells(i, 5) = "" Then
If Cells(i, 6) = "" Then
If Cells(i, 7) = "" Then
If Cells(i, 8) = "" Then
If Cells(i, 9) = "" Then
If Cells(i, 10) = "" Then
If Cells(i, 11) = "" Then
If Cells(i, 12) = "" Then
If Cells(i, 13) = "" Then
If Cells(i, 14) = "" Then
Rows(i).Select
Selection.Delete Shift:=xlUp
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
i = i + 1
Next

End Sub

Which I have found my answer in another post:
http://www.mrexcel.com/forum/showthread.php?t=536949&highlight=delete+empty+rows