MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Deleting All Blank Cells after importing a text file


Posted by Rodney on January 15, 2001 5:40 PM

Hello,

I am using Excel 2000.
I am trying to import a text file that is column delimited and contains three separate reports all having different column sizes. I have been very lucky constructing macros to do the work I need. I use the following to import the text.

Public Sub DoTheImport()
Dim FName As Variant
Dim Sep As String

FName = Application.GetOpenFilename _
(filefilter:="All Files (*.*),*.*")
If FName = False Then
MsgBox "You didn't select a file"
Exit Sub
End If

Sep = InputBox("Enter a single delimiter character.", _
"Import Text File")
ImportTextFile CStr(FName), Sep

End Sub

Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub


Using a space for delimitation
I than go through an delete blank cells with the following
Public Sub deleteblankcells()

On Error Resume Next
Selection.SpecialCells(xlCellblanks).Delete
ActiveSheet.UsedRange

End Sub

I can than clean up rows and align the columns with macros.
My problem- I need to import a date in one of the columns. If I import the text with the macro above the date does import correctly. For example August 01,2000 imports as two cells only as August 12,000. To fix this I ran a macro before the import that formatted all cells as Text. Problem is my delete blank cells macro no longer works. My question is how do I delete the blank cells. The deletion is very essential to formatting the data correctly. I should also mention that the number of rows change from import to import, with a couple of Blank rows being added when data meets certain critera (the data is being generated by an IBM Main Frame). I address the row dilemma by using a delete row function. Any help is greatly appreciated.

Rodney


Posted by thomas venn on January 16, 2001 4:51 PM

how about parsing (text to columns) your date field. XL should recognize and format the date correctly if you parse it and choose Date option.

Cheers,
thomas

Posted by Dave Hawley on January 17, 2001 3:03 AM

Cheers,

Hi Rodney

I don't quite understand why you say that having cells formatted as Text is causing problems with blank cell deletion. I also do not understand why you have: ActiveSheet.UsedRange below the SpecialCells code ?

Try it like this:

ActiveSheet.UsedRange.SpecialCells(xlBlanks).Delete

Dave

  • OzGrid Business Applications