VBA If statement to skip file if first row is blank

Question

I have a code that compiles data from many company files into one large file. Some files will have blank values for the FirstRow so it pulls in the headers and blank cells. Instead, I would like to use an If statement to skip the file if the FirstRow is blank. Here is the code currently:

    Dim Summary As Worksheet
    Dim FolderPath As String
    Dim SelectedFiles() As Variant
    Dim nrow As Long
    Dim FileName As String
    Dim nfile As Long
    Dim wb As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range
    Dim LastRow As Long
    Dim FirstRow As Long

    'Set Summary as the current excel file in which the macro will run
    Set Summary = ActiveWorkbook.Sheets(1)

    'Modify this folder path to point to the folder that contains copies of the commission statements
    FolderPath = "C:\Users\stroychak\Dropbox (Apttus)\Commission Folder\000 Commission Statements\z - All Commissions\Commission Summary VBA - statements"


    'Set the current directory and drive to the desired folder path
    ChDrive FolderPath
    ChDir FolderPath

    'Open the file dialogue box to select the commission statements to be compiled; allow for multiple statements to be selected at once
    SelectedFiles = Application.GetOpenFilename(MultiSelect:=True)

    'nrow keeps track of where to insert new rows in the destination workbook
    nrow = 1

    For nfile = LBound(SelectedFiles) To UBound(SelectedFiles)
        FileName = SelectedFiles(nfile)
        Set wb = Workbooks.Open(FileName)
        LastRow = wb.Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row
        FirstRow = wb.Worksheets(1).Cells.Find("Opportunity Name").Row + 1
        Set SourceRange = wb.Worksheets(1).Range("A" & FirstRow & ":AB" & LastRow)
        Set DestRange = Sheet1.Range("A" & nrow)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, SourceRange.Columns.Count)

        DestRange.Value = SourceRange.Value

        nrow = DestRange.Rows.Count + nrow

        wb.Close savechanges = False

    Next nfile


    ActiveSheet.Columns.AutoFit

MsgBox "Compilation is complete"

End Sub

Possible duplicate of [how to find out if an entire row is blank in excel through vba](https://stackoverflow.com/questions/3628057/how-to-find-out-if-an-entire-row-is-blank-in-excel-thorough-vba) — BigBen, Jul 25 '18 at 20:25
So what is in the first row that you would check? It could be blank. It could have data. It could contain certain keywords that would indicate a header row. These are things that you know, which are not shared in your post. Take what you know and check those cells. Show us your `If` statement if it's not working. — PeterT, Jul 25 '18 at 20:26
FirstRow = wb.Worksheets(1).Cells.Find("Opportunity Name").Row + 1 The FirstRow is based on the Cell containing "Opportunity Name" The cell will either be blank or contain data. If the cell is blank I want to move on the the next file. — stroych, Jul 25 '18 at 20:34
problem is you add 1, Find returns 0 if not found, so would your if not be if FirstRow <> 1 Then do your next five statements Else Do nothing, End if, let the workbook close, go to next file? — Wookies-Will-Code, Jul 25 '18 at 21:00
your find should be throwing an error when not found, FirstRow will remain unchanged from the default of 0, since you are in a loop you better be sure to always set it to 0 — Wookies-Will-Code, Jul 25 '18 at 21:15

Wookies-Will-Code · Answer 1 · 2018-07-25T21:14:14.663

How does this work for you?

Dim Summary As Worksheet
Dim FolderPath As String
Dim SelectedFiles() As Variant
Dim nrow As Long
Dim FileName As String
Dim nfile As Long
Dim wb As Workbook
Dim SourceRange As Range
Dim DestRange As Range
Dim LastRow As Long
Dim FirstRow As Long

'Set Summary as the current excel file in which the macro will run
Set Summary = ActiveWorkbook.Sheets(1)

'Modify this folder path to point to the folder that contains copies of the commission statements
FolderPath = "C:\Users\stroychak\Dropbox (Apttus)\Commission Folder\000 Commission Statements\z - All Commissions\Commission Summary VBA - statements"


'Set the current directory and drive to the desired folder path
ChDrive FolderPath
ChDir FolderPath

'Open the file dialogue box to select the commission statements to be compiled; allow for multiple statements to be selected at once
SelectedFiles = Application.GetOpenFilename(MultiSelect:=True)

'nrow keeps track of where to insert new rows in the destination workbook
nrow = 1

For nfile = LBound(SelectedFiles) To UBound(SelectedFiles)
    FirstRow = 0 'Set this, if you forget you will have mixed results
    FileName = SelectedFiles(nfile)
    Set wb = Workbooks.Open(FileName)
    LastRow = wb.Worksheets(1).Cells(Rows.Count, "A").End(xlUp).Row

    On Error Resume Next 'If it fails FirstRow remains 0
    FirstRow = wb.Worksheets(1).Cells.Find("Opportunity Name").Row + 1

    If FirstRow <> 0 Then
        'Found it
        Set SourceRange = wb.Worksheets(1).Range("A" & FirstRow & ":AB" & LastRow)
        Set DestRange = Sheet1.Range("A" & nrow)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, SourceRange.Columns.Count)

        DestRange.Value = SourceRange.Value

        nrow = DestRange.Rows.Count + nrow
    Else 'Do Nothing, Didn't find it
    End If

    wb.Close savechanges = False

Next nfile


ActiveSheet.Columns.AutoFit

MsgBox "Compilation is complete"

End Sub

VBA If statement to skip file if first row is blank

1 Answers1