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

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