Deleting text file lines

[RomanR]

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

 

[Guest]

Try this
Sub DeletePound()
Columns("A:A").AutoFilter Field:=1, Criteria1:="=#*", Operator:=xlAnd
Range("A1").SpecialCells(xlCellTypeVisible).EntireRow.Delete
End Sub

 

[Jake Marx]

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]

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