title: VBA: Next Empty Row Randomly Overwriting Some Rows
body: <p>I have a list of files:
  <a href="https://i.stack.imgur.com/emmBR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/emmBR.png" alt="Files"></a></p>
  
  <p>They share one common format, only have one sheet, but can have multiple rows with data. They are meant to be opened, all cells with data copied, and then pasted to a sheet called Addresses. Like this:</p>
  
  <p><a href="https://i.stack.imgur.com/DpACA.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DpACA.png" alt="WhatIWant"></a></p>
  
  <p>However what I'm getting is this:
  <a href="https://i.stack.imgur.com/bU5JD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bU5JD.png" alt="WhatIHave"></a></p>
  
  <p>Now I have stepped in and noticed that my other data IS being put in the destination, it's just getting overwritten (in what appears to be a random pattern). Here's the code I used:</p>
  
  <pre><code>Option Explicit
  Sub AddressListing()
    Dim Cell As Range
    With Worksheets("ghgh")
      For Each Cell In .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Cells
        If Len(Dir(Cell.Value)) Then
          With Workbooks.Open(Cell.Value)
            Range("A2:X" &amp; Cells(Rows.Count, "D").End(xlUp).Row).copy _
                ThisWorkbook.Worksheets("Addresses").Cells(Rows.Count, "A").End(xlUp).Offset(1)
            .Close SaveChanges:=False
          End With
        Else
          MsgBox "File not found: " &amp; Cell.Value
        End If
      Next Cell
    End With
  'Call RemoveViaFilter
  End Sub
  </code></pre>
  
  <p>In an effort to combat this and not waste everyone's time, I created a NextRow variable to find the next blank row in the workbook. It still didn't work. I don't get an error message, the data is simply input the same way. 
  Here's the code with NextRow:</p>
  
  <pre><code>Option Explicit
  Sub AddressListing2()
    Dim Cell As Range
    Dim NextRow As Long
    NextRow = ThisWorkbook.Sheets("Addresses").Range("D" &amp; Rows.Count).End(xlUp).Row + 1
    With Worksheets("ghgh")
      For Each Cell In .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Cells
        If Len(Dir(Cell.Value)) Then
          With Workbooks.Open(Cell.Value)
            Range("A2:X" &amp; Cells(Rows.Count, "D").End(xlUp).Row).copy _
                ThisWorkbook.Worksheets("Addresses").Range("A" &amp; NextRow)
            .Close SaveChanges:=False
          End With
        Else
          MsgBox "File not found: " &amp; Cell.Value
        End If
      Next Cell
    End With
  'Call RemoveViaFilter
  End Sub
  </code></pre>
  
  <p>I have never encountered that type of error with NextRow. I know 'Find next blank row and put data there' is a common question, which is why I thought NextRow would solve the issue. However, data is still being overwritten and I have not come across any questions that address this.</p>
  
  <p><em>I don't want defined ranges (like A2:J100 for example) and have purposefully avoided them</em>, because the length of my lists constantly changes. That goes for the rows I want to paste and the rows of file paths.</p>
  
  <p>Any help is much appreciated, I've used the 'find empty row' several times before with no issues, and don't know why it's overwriting data. It seems antithetical to the whole process of find the empty row.</p>