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

Asked

Viewed 1,138 times

3

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
  • 2

    What macro is this?

  • Post the code of your macro

  • 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 . Preserverveformatting = 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

  • I’m sorry, but I was only able to put in the fractional code. Need that after imported the TXT file that is updated daily it sends the necessary information leaves a blank line and then on the line below (9 Rows(s) affected) <- I want to delete this guy Please help me

  • Do not put the code in comments. Edit the question to insert the code into it! :)

Show 5 more comments

2 answers

0

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

0

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

Browser other questions tagged

You are not signed in. Login or sign up in order to post.