Web Development

General discussion

VBA If statement to skip file if first row is blank

By priyankakumari3058 ·
Tags: Software
I have a Apttus CLM Training 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"
Thread display: Collapse - | Expand +

All Comments

Collapse -

Re: skip

by Kees_B Moderator In reply to VBA If statement to skip ...

I see 7 lines that do the actual copy.

Now find a way to check if the first row is blank. You might need to test cells of the row in stead of the whole row (blank isn't an attribute for rows, only for cells). And remember that a cell filled with a space or an empty string is not empty, it only looks empty.
Since you didn't specify what exactly you mean with "blank values for the FirstRow" you'll have to find out the details first, since we can't see the files.

Then put those 7 lines inside an if, something like:

if <first_line is not empty> then
<do the copy>
end if

Since this seems to be for a company, the companies IT support should be able to help you.

By the way, you posted in the wrong forum. Your question has nothing to do with Web Development.

Related Discussions

Related Forums