VBA: Next Empty Row Randomly Overwriting Some Rows

R.E.L.

I have a list of files: Files

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:

WhatIWant

However what I'm getting is this: WhatIHave

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:

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" & 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: " & Cell.Value
      End If
    Next Cell
  End With
'Call RemoveViaFilter
End Sub

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:

Option Explicit
Sub AddressListing2()
  Dim Cell As Range
  Dim NextRow As Long
  NextRow = ThisWorkbook.Sheets("Addresses").Range("D" & 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" & Cells(Rows.Count, "D").End(xlUp).Row).copy _
              ThisWorkbook.Worksheets("Addresses").Range("A" & NextRow)
          .Close SaveChanges:=False
        End With
      Else
        MsgBox "File not found: " & Cell.Value
      End If
    Next Cell
  End With
'Call RemoveViaFilter
End Sub

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.

I don't want defined ranges (like A2:J100 for example) and have purposefully avoided them, because the length of my lists constantly changes. That goes for the rows I want to paste and the rows of file paths.

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.

Darrell H

This is where to you put the additional line...

   Option Explicit
    Sub AddressListing2()
      Dim Cell As Range
      Dim NextRow As Long
      NextRow = ThisWorkbook.Sheets("Addresses").Range("D" & 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" & Cells(Rows.Count, "D").End(xlUp).Row).copy _
                  ThisWorkbook.Worksheets("Addresses").Range("A" & NextRow)
              .Close SaveChanges:=False
            End With
          Else
            MsgBox "File not found: " & Cell.Value
          End If
        'Add line here before going to new loop
        NextRow = ThisWorkbook.Sheets("Addresses").Range("D" & Rows.Count).End(xlUp).Row + 1
        Next Cell
      End With
    'Call RemoveViaFilter
    End Sub

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

VBA - Skipping rows and overwriting the row

vba excel userform move to next empty row

Array row is overwriting another rows

Concatenate Rows If Value of Another Column In Next Row Is Empty

Copying and Paste multiple rows into next empty Row on another sheet

Loop through rows and when empty move to next column - vba

finding next empty row

Excel VBA Copy/Paste overwriting same row

Are there some efficient ways to find row(s) meeted conditons which referred to the values in next some rows?

VBA Next available row

How to randomly select some pandas dataframe rows?

Returns the value in the next empty rows

Copy/Paste last two rows into next empty row and clear certain cells (contains merged cells)

Calculate value of all numeric variables for empty rows based on previous and next row of same id

Weird df.sort_values result - randomly leaves multiple rows empty but missing values are misplaced a row down in the wrong columns

Autofill Copy down up until next empty row- Excel VBA Macro

How to get a listbox to return it's results into the next empty row in excel vba

Copy rows from one sheet into other sheet and after 7 columns go to next row using vba in excel

VBA Loop: if cell contains specific value then delete the row containing the value and the next 3 rows below

Excel VBA to copy multiple rows and insert to next row depending on button click

Excel VBA Formula on next row

How to exclude some rows in VBA

Delete row i and next row in VBA

Range, empty row in Excel vba

VBA copy rows to empty rows in other worksheet

Get rows with some value next to threshold

How can I randomly change the values of some rows in a pandas DataFrame?

Move next value to first empty row pandas

Write to next empty row in matrix in Matlab