Text file consolidation and deletion of blank rows

[watiwawa]

Good day guys!


Can you please help me on this?




Here are the parts of my project and its code.


My project is to consolidate data from text file(.txt)
*note that i can select multiple text file, in which 1 text file = 1 sheet
*What is happening is that it is opening a new workbook
*How can I make it open to just the same workbook but just different sheet


Here is my code for that part:

Private Sub CommandButton1_Click()


 Dim FilesToOpen
    Dim x As Integer
    Dim wkbAll As Workbook
    Dim wkbTemp As Workbook
    Dim sDelimiter As String


    On Error GoTo ErrHandler
    Application.ScreenUpdating = False


    sDelimiter = ";"


    FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="Text Files (*.txt), *.txt", _
      MultiSelect:=True, Title:="Text Files to Open")


    If TypeName(FilesToOpen) = "Boolean" Then
        MsgBox "No Files were selected"
        GoTo ExitHandler
    End If


    x = 1
    Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
    wkbTemp.Sheets(1).Copy
    Set wkbAll = ActiveWorkbook
    wkbTemp.Close (False)
    wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
      Destination:=Range("A1"), DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Tab:=False, Semicolon:=True, _
      Comma:=False, Space:=False, _
      Other:=True, OtherChar:=";"
    x = x + 1


    While x <= UBound(FilesToOpen)
        Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
        With wkbAll
            wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
            .Worksheets(x).Columns("A:A").TextToColumns _
              Destination:=Range("A1"), DataType:=xlDelimited, _
              TextQualifier:=xlDoubleQuote, _
              ConsecutiveDelimiter:=False, _
              Tab:=False, Semicolon:=False, _
              Comma:=False, Space:=False, _
              Other:=True, OtherChar:=sDelimiter
        End With
        x = x + 1
    Wend


ExitHandler:
    Application.ScreenUpdating = True
    Set wkbAll = Nothing
    Set wkbTemp = Nothing
    Exit Sub


ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
    
End Sub

And then delete rows that has blank in selected columns.
*can I make an excemption and choose only 2 columns?
*also note that different text files also has different number of columns.


Here is my code for the deletion part of rows part

Private Sub CommandButton2_Click()
Dim lc As Integer, lr As Long, r As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "A").End(xlUp).row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
 For c = 1 To lc
    For r = lr To 2 Step -1
        If Cells(r, c) = "" Then Rows(r).Delete
    Next r
 Next c
Application.ScreenUpdating = True


End Sub

Can you pleas help me combine this two codes and make it that after consolidation, rows with blank are already deleted. Thank you
I've already tried different techniques but still to no avail.
New set of codes will be highly appreciated if it is for the best.