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

Question

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

Navigation

#1 by (0 votes)
#2 by (0 votes)

3

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

(9 rows (s) affected)

The information of the data is correct, however it has 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:\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

macro vba

asked by anonymous 06.03.2015 / 20:53

2 answers

Android + OpenCV: SurfaceTexture has been abandoned How to separate PHP code from HTML? [closed]

score 0 · Answer 1

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

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

But as I do not know your VBA level, I am sending the code below to delete the last imported line. This code should 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

score 0 · Answer 2

I believe it would be best to load all the data and then create a macro to select the empty rows 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 statement shows you the total of filled rows.

Then scan the column and identify the empty line and delete it.

For x = 1 to Total_Linhas  

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

next x