General discussion

  • Creator
  • #2140470

    VBA If statement to skip file if first row is blank

    by priyankakumari3058 ·


    I have a [url=]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 = 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”

You are posting a reply to: VBA If statement to skip file if first row is blank

The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to our Community FAQs for details. All submitted content is subject to our Terms of Use.

All Comments

  • Author
    • #2416542
      Avatar photo

      Re: skip

      by kees_b ·

      In reply to VBA If statement to skip file if first row is blank

      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 then

      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.

Viewing 0 reply threads