VBA Import, Merge & Modify files

[amaluna]

Hello,
I have the following code which lets me pick multiple files from a specific folder and then merges all of them together. The code works great, except I ran into the issue where some files had a blank Column A and since the code is trying to find the last used row before pasting the new data, it's overwriting the previous data. Can someone help me modify the code so that when you select these files it removes column A and then pastes the data in the sheet I want?

Thank you in advance!

Sub select_merge()    Dim Cnt As Long
    Dim destCell As Range
    Dim fd As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    Dim vrtSelectedItem As Variant
    Application.ScreenUpdating = False
 
    Sheets("input").UsedRange.ClearContents

    With fd
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
                Cnt = Cnt + 1
                If Cnt = 1 Then
                    Set destCell = Worksheets("input").Range("A1")
                Else
                    Set destCell = Worksheets("input").Cells _
                    (Rows.Count, "A").End(xlUp).Offset(1)
                End If
                
                If Cnt = 1 Then
                    With destCell.Parent.QueryTables.Add(Connection:="TEXT;" & _
                    vrtSelectedItem, Destination:=destCell)
                        .TextFileStartRow = 1
                        .TextFileParseType = xlDelimited
                        .TextFileCommaDelimiter = True
                        .Refresh BackgroundQuery:=False
                    End With
                Else
                    With destCell.Parent.QueryTables.Add(Connection:="TEXT;" & _
                    vrtSelectedItem, Destination:=destCell)
                        .TextFileStartRow = 2
                        .TextFileParseType = xlDelimited
                        .TextFileCommaDelimiter = True
                        .Refresh BackgroundQuery:=False
                    End With
                End If
            Next vrtSelectedItem

            If Cnt = 0 Then _
                MsgBox "No CSV files were found...", vbExclamation
        End If
    End With
    Set fd = Nothing
     
    Application.ScreenUpdating = True
End Sub

 

[Joe4]

The code works great, except I ran into the issue where some files had a blank Column A and since the code is trying to find the last used row before pasting the new data

Is there some column, other than column A, you can use to locate the last row of data (in other words, is there some column that will ALWAYS be populated for every row with data)?

 

[amaluna]

yes every other column other than A

 

[Joe4]

Then try changing this line:

Set destCell = Worksheets("input").Cells _
                    (Rows.Count, "A").End(xlUp).Offset(1)

to this:

Set destCell = Worksheets("input").Cells _
                    (Rows.Count, "B").End(xlUp).Offset(1,-1)

 

[amaluna]

thank you so much!!

 

[Joe4]

You are welcome!