How to delete 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 down after a white line

(9 Rows(s) affected)

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

This via VBA code.

Edited: macro code based on the 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 1

From what I understand the phrase "(9 Rows(s) affected)" comes along with the file . TXT’s name "Promessas_diarias_aes_rc_20150304_210000" you are trying to import. From what I understood also this macro was developed by Excel write mode.

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

But as I do not know your VBA level, I am sending the code below to delete the last imported line. That code shall be inserted after its 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

Answer 2

I believe that it would be best to load all the data and then create a macro to select the empty lines and delete.

To identify the total number of lines you could use the code:

Total_Linhas = cells(rows.count,1).end(xlup).offset(0,0).row

This instruction shows you the total of filled rows.

Then scan the column and identify the empty row and delete.

For x = 1 to Total_Linhas  

    if range("A" & x).value = "" then
      range("A" & x).delete shift:= xlUP  
    end if

next x