Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

VBA If statement to skip file if first row is blank

Level 1
Level 1

I have a code that compiles apttus 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


MsgBox "Compilation is complete"

End Sub


0 Replies