views:

94

answers:

0

I've tried this but it doesn't work. it skips the rows that have A[i] empty. Any help is appreciated.

Sub Consolidate()
''# Local Variables
    Dim cell, cell2 As Range
    Dim wks As Worksheet

''# Step 1: Clear master
''# Step 2 : Loop through the regional sheets
    Sheets("Master").Range("A2:Z65536").ClearContents
    For Each wks In ThisWorkbook.Worksheets
        If wks.Name <> "Master" Then
            For Each cell In wks.Range("A2", "A1000")
                If cell.Value = vbNullString Then
                    cell.EntireRow.Copy Destination:=Worksheets("Master").Range("A65536").End(xlUp).Offset(1, 0)
                Else
                    cell.EntireRow.Copy Destination:=Worksheets("Master").Range("A65536").End(xlUp).Offset(1, 0)
                End If
            Next cell
        End If
    Next wks
End Sub