General discussion
-
Topic
-
VBA If statement to skip file if first row is blank
I have a [url=https://hkrtrainings.com/apttus-clm-training]Apttus CLM Training[/url] 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 = 1For 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”
