Closed Thread
Results 1 to 4 of 4

Deleting text file lines

Hybrid View

  1. #1
    Jake Marx
    Guest

    Re: Deleting text file lines

    Hi Roman,

    Do you want to delete the rows prior to importing the tab-delimited file?
    Or do you want to import the file, then delete the comment rows once you
    have it in Excel? If the latter, this would work after the import:

    Sub RemoveComments()
    Dim rngTarget As Range
    Dim rng As Range
    Dim lLastRow As Long
    Dim lRow As Long

    Application.ScreenUpdating = False

    With ActiveSheet
    lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row

    For lRow = 1 To lLastRow
    Set rng = .Cells(lRow, 1)
    If Left$(rng.Value, 1) = "#" Then
    If rngTarget Is Nothing Then
    Set rngTarget = rng
    Else
    Set rngTarget = Application.Union(rng, rngTarget)
    End If
    End If
    Next lRow

    If Not rngTarget Is Nothing Then
    rngTarget.EntireRow.Delete xlUp
    End If
    End With

    Application.ScreenUpdating = True

    Set rng = Nothing
    Set rngTarget = Nothing
    End Sub

    --
    Regards,

    Jake Marx
    www.longhead.com


    [please keep replies in the newsgroup - email address unmonitored]


    RomanR wrote:
    > Hi all
    >
    > I'm writing my first Excel/VBA macro, and having some trouble doing a
    > certain function. I know this is probably really basic stuff, but in
    > all my searches I haven't been able to find any documentation on
    > deleting text file lines. The data in the text file that I have to
    > import into excel is in the following format:
    >
    > # aaaaaaa bbbbbb ccccc
    > # aaaaaaa bbbbbb ccccc
    > # aaaaaaa bbbbbb ccccc
    > # aaaaaaa bbbbbb ccccc
    > Aaaaaaa bbbbbb ccccc
    > Aaaaaaa bbbbbb ccccc
    > Aaaaaaa bbbbbb ccccc
    > Aaaaaaa bbbbbb ccccc
    > # aaaaaaa bbbbbb ccccc
    > # aaaaaaa bbbbbb ccccc
    > Aaaaaaa bbbbbb ccccc
    > Aaaaaaa bbbbbb ccccc
    >
    > The lines that begin with '#' are comments, and don't need to be
    > imported into the worksheet. The rest of the lines do. How do I go
    > about writing a do-while loop that will delete the comment lines? I
    > know how to program in Java pretty well, so I know the logic behind
    > this process, I just don't know the vba language. Not that it makes
    > any difference, but the fields are tab delimited, so the file is
    > displayed nicely in excel once it is imported. All I have left is
    > deleting those comment lines, any help you guys can give me will be
    > hugely appreciated.
    >
    > Thanks in advace,
    >
    > Roman




  2. #2
    RomanR
    Guest

    Re: Deleting text file lines

    Excellent stuff guys, both subroutines did exactly what I needed. Jake,
    it doesn't matter whether the formatting steps happen before or after
    the file is imported, but as you said it is much easier if the file is
    opened in excel first. I also had to find a subroutine that would go
    through and delete blank rows.

    Thanks much again,

    Roman


Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1