vba – How to delete a line with information after a blank line in the middle?

Question:

I'm running a macro to import txt data. But this macro is bringing the information below after a white line

(9 row(s) affected)

The data information is correct, but there is this line that I need the macro to delete.

This via VBA code.

Edited: macro code based on comments

    Sub XPTO()    
        With ActiveSheet.QueryTables.Add(Connection:= "TEXT;Z:\42\Promessas_diarias_AES_RC_20150304_210000.txt", Destination:= Range(Selection.Address))
           .Name = "Promessas_diarias_AES_RC_20150304_210000" 
           .FieldNames = True 
           .RowNumbers = False 
           .FillAdjacentFormulas = False 
           .PreserveFormatting = True 
           .RefreshOnFileOpen = False 
           .RefreshStyle = xlInsertDeleteCells 
           .SavePassword = False 
           .SaveData = True 
           .AdjustColumnWidth = True 
           .RefreshPeriod = 0 
           .TextFilePromptOnRefresh = False 
           .TextFilePlatform = 1252 
           .TextFileStartRow = 5 
           .TextFileParseType = xlDelimited 
           .TextFileTextQualifier = xlTextQualifierDoubleQuote 
           .TextFileConsecutiveDelimiter = False 
           .TextFileTabDelimiter = True 
           .TextFileSemicolonDelimiter = True 
           .TextFileCommaDelimiter = False 
           .TextFileSpaceDelimiter = False 
           .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1) 
           .TextFileTrailingMinusNumbers = True 
           .Refresh BackgroundQuery:=False 
       End With 
       ActiveWindow.SmallScroll Down:=6 
       Range("F569:L570").Select 
       Selection.ClearContents 
       Range("F569").Select 
   End Sub

   Sub OptionPane() 
       MsgBox (Selection.Address) 
       MsgBox Day(Date) 
       MsgBox Month(Date) 
       MsgBox Year(Date) 
   End Sub

Answer:

As I understand it, the phrase "(9 rows(s) affected)" comes with the .TXT file named "Promessas_diarias_AES_RC_20150304_210000" that you are trying to import. From what I understand this macro was also developed by Excel recording mode.

Well, you can search for other ways to use VBA to import the file. For example using a loop repeat and a condition to stop the import when the word "rows" is written in the sentence.

But since I don't know your VBA level, I'm sending the code below to delete the last imported line. This code must be inserted after your import code.

    ' Substituir o conteudo entre aspas duplas das variaveis constantes abaixo
    ' para as informações da sua planilha.
    Const _
        colunaDaFrase = "A", _
        nomeAbaExcel = "Plan1"

    ' Abaixo o código está encontrando a ultima linha preenchida na colua
    ' fornecida pela variável acima.
    Dim ultimaLinha As Long
    ultimaLinha = ThisWorkbook.Sheets(nomeAbaExcel).Range(colunaDaFrase & Rows.Count).End(xlUp).Row

    ' Abaixo o código está apagando a identificada como "última"
    ThisWorkbook.Sheets(nomeAbaExcel).Range(ultimaLinha & ":" & ultimaLinha).Delete Shift:=xlUp
Scroll to Top