importing text file and deleting empty rows and rogue data 1

[smurf01]

Hi, I am importing a text file into excel, the one problem I have is that the text file comes from multiple pages and therefore I have at random intervals two blank rows plus one row that contains the headers from the subsequent pages of the text file.

Does anyone know how i can serch for these rows and delete them. Any help would be much appreciated

Regards

Paul

 

[VBAjedi]

Heres a piece of code I just banged out to looks for two adjacent empty rows, and delete them and the row below them wherever found. Just change the worksheet name in the Sub and in the Function to match your worksheet.

Sub DeleteGarbage()
Dim x, y, z
z = InputBox("Scan how many rows?", , "1000")
For x = z To 1 Step -1 ' work from bottom up
   If EmptyRow(x) And EmptyRow(x + 1) Then
      Worksheets("Sheet1").Rows(x & ":" & (x + 2)).Delete
   End If
Next x
End Sub

Function EmptyRow(RowNum)
Dim c As Range
Set c = Worksheets("Sheet1").Rows(RowNum & ":" & RowNum).Find(what:="*")
If c Is Nothing Then
   EmptyRow = True
End If
End Function

Let me know if that does what you are after!

VBAjedi

 

[smurf01]

VBAjedi,
very nice piece of code and it works beautifully.
A "star" for you



Regards

Paul

 

[smurf01]

VBAjedi,
As i said the code works great, just one more question ?
The text file I import can have varying amounts of rows, is there anyway that I could get the number of the last row into the input box.

e.g. If the file contained 250 rows then the default value in the input box would be 250, or if the number of rows was 379 then that would transfer to the input box as the default value.

The reason I ask is that I have included your code into the code I already had for importing the data in, stripping out the rubbish and reformatting some data, therefore as the code is already running you cannot see what the last row is.

I hope I have explained this correctly

Regards

Paul

 

[VBAjedi]

Lots of approaches to finding the last row. The one I'm partial to uses the Find method to find the last cell with contents (should work well in your situation). If the user doesn't really have visibility to know what the last row is anyway, you might want to take out the input box altogether and just use something like this code to decide what the last row is:

LastRow = Worksheets("Sheet1&quot.Cells.Find(what:="*", after:=Range("IV65536&quot, _
searchorder:=xlByRows, searchdirection:=xlPrevious).Row

As you can see, it's a similar approach to what I use to test for an empty row, with the addition of some very important parameters to tell the search where to start and in what direction to work.

VBAjedi

 

[smurf01]

VBAjedi,
Sorry to be a nuisance, but how would I incorporate this new code into the other code ??

Regards

Paul

 

[VBAjedi]

On way out the door, no time to test, but try:

Sub DeleteGarbage()
Dim x, y, z
z = Worksheets("Sheet1").Cells.Find(what:="*", after:=Range("IV65536"), _
    searchorder:=xlByRows, searchdirection:=xlPrevious).Row

For x = z To 1 Step -1 ' work from bottom up
   If EmptyRow(x) And EmptyRow(x + 1) Then
      Worksheets("Sheet1").Rows(x & ":" & (x + 2)).Delete
   End If
Next x
End Sub

Function EmptyRow(RowNum)
Dim c As Range
Set c = Worksheets("Sheet1").Rows(RowNum & ":" & RowNum).Find(what:="*")
If c Is Nothing Then
   EmptyRow = True
End If
End Function

Let me know tomorrow if that got it!


VBAjedi

 

[smurf01]

VBAjedi
Thanks very much, code works fine



Regards

Paul