title: Trying to use Excel VBA to skip blank rows in my copy/paste loop
body: <p>I have a copy / paste loop for line items in an Excel file that exports data from these line items into an Excel-based form and saves each form by the value in Row B. My issue is that these line items are divided into 3 different tables on the same sheet, each with a different number of line items to be copied. Furthermore, each table is separated by 2 blank rows.</p>
  
  <p>What I need the macro to do for me: </p>
  
  <ol>
  <li>Start at line 17 and copy all line items in the first table until it hits a blank row - this varies from 1 to 600 rows.</li>
  <li>Skip to SecondTable and perform the same functions.</li>
  <li>Repeat for ThirdTable</li>
  </ol>
  
  <p>Ignore some of the declarations as I deleted a large chunk of code for readability. I figured I would need 3 separate copy/paste loops to accomplish this (I've only included 2 here) and I tried using .Find to reference the start of the second/third tables. The macro runs as normal through the first table, but doesn't stop when it hits a blank row and fails when it tries to save a file based on the value of an empty cell. I believe the issue lies with the <code>EndOne = .Range("B" &amp; .Rows.Count).End(xlUp).Row</code> argument right under <code>With wsSource</code>. Instead of counting only the non-blank rows of the first table, it counts the number of rows through the end of the third table. </p>
  
  <pre><code>Sub CopyToForm()
  
  Dim wbSource As Workbook, wbForm As Workbook
  Dim wsSource As Worksheet, wsForm As Worksheet
  Dim formpath As String, foldertosavepath As String
  Dim EndOne As Long, EndTwo As Long, EndThree As Long, i As Integer
  Dim strProcessingFormPath As String
  'Dim strCancel As String
  'Dim strFilt As String
  'Dim intFilterIndex As Integer
  'Dim strDialogueFileTitle As String
  Dim SecondTable As String
  Dim ThirdTable As String
  
  Set wbSource = ThisWorkbook '~~&gt; Write your code in Indication Tool.xls
  Set wsSource = wbSource.Sheets("Indication Tool") '~~&gt; Put the source sheet name
  
  
  With wsSource
      '~~&gt; Counts how many rows are in the Indication Tool
      EndOne = .Range("B" &amp; .Rows.Count).End(xlUp).Row
      If EndOne &lt; 17 Then MsgBox "No data for transfer": Exit Sub
      For i = 17 To EndOne
          Set wbForm = Workbooks.Open(formpath) '~~&gt; open the form
          Set wsForm = wbForm.Sheets("Processing Form") '~~&gt; Declare which worksheet to activate
          '~~&gt; Proceed with the copying / pasting of values
          .Range("B" &amp; i).Copy wsForm.Range("F7:K7")
          .Range("C" &amp; i).Copy: wsForm.Range("D8").PasteSpecial xlPasteValues
          .Range("C" &amp; i).Copy: wsForm.Range("D30").PasteSpecial xlPasteValues
          .Range("D" &amp; i).Copy: wsForm.Range("H29").PasteSpecial xlPasteValues
          .Range("E" &amp; i).Copy: wsForm.Range("E29").PasteSpecial xlPasteValues
          .Range("F" &amp; i).Copy: wsForm.Range("D33").PasteSpecial xlPasteValues
          .Range("G" &amp; i).Copy: wsForm.Range("K30").PasteSpecial xlPasteValues
          .Range("H" &amp; i).Copy: wsForm.Range("P33").PasteSpecial xlPasteValues
          .Range("L" &amp; i).Copy: wsForm.Range("H32").PasteSpecial xlPasteValues
          .Range("R" &amp; i).Copy: wsForm.Range("D87").PasteSpecial xlPasteValues
          '.Range("C5:M5").Copy: wsForm.Range("E102").PasteSpecial xlPasteValues
          '~~&gt; Save the form using the value in cell i,B
          wbForm.SaveAs .Range("B" &amp; i).Value &amp; ".xls"
          wbForm.Close
          Set wbForm = Nothing
          Set wsForm = Nothing
     Next
  
  End With
  
  With wsSource
      SecondTable = .Range("B:B").Find("SecondTable").Row
      EndTwo = .Range("B" &amp; .Rows.Count).End(xlUp).Row
      For i = Second Table + 1 To EndTwo
          Set wbForm = Workbooks.Open(formpath) '~~&gt; open the form
          Set wsForm = wbForm.Sheets("Processing Form") '~~&gt; Declare which worksheet to activate
          '~~&gt; Proceed with the copying / pasting of values
          .Range("B" &amp; i).Copy wsForm.Range("F7:K7")
          .Range("C" &amp; i).Copy: wsForm.Range("D8").PasteSpecial xlPasteValues
          .Range("C" &amp; i).Copy: wsForm.Range("D30").PasteSpecial xlPasteValues
          .Range("D" &amp; i).Copy: wsForm.Range("H29").PasteSpecial xlPasteValues
          .Range("E" &amp; i).Copy: wsForm.Range("E29").PasteSpecial xlPasteValues
          .Range("F" &amp; i).Copy: wsForm.Range("D33").PasteSpecial xlPasteValues
          .Range("G" &amp; i).Copy: wsForm.Range("K30").PasteSpecial xlPasteValues
          .Range("H" &amp; i).Copy: wsForm.Range("P33").PasteSpecial xlPasteValues
          .Range("L" &amp; i).Copy: wsForm.Range("H32").PasteSpecial xlPasteValues
          .Range("R" &amp; i).Copy: wsForm.Range("D87").PasteSpecial xlPasteValues
          .Range("C5:M5").Copy: wsForm.Range("E102").PasteSpecial xlPasteValues
          '~~&gt; Save the form using the cells i,B
          wbForm.SaveAs .Range("B" &amp; i).Value &amp; ".xls"
          wbForm.Close
          Set wbForm = Nothing
          Set wsForm = Nothing
     Next
  
  End With
  
  End Sub
  </code></pre>
  
  <p>Am I on the right track with the .Find and a separate copy/paste loop for each table? I realize this is a complex problem and I appreciate any time you take to spend helping me out.</p>