SKIP EMPTY ROWS FROM IMPORT

  • Thread starter Thread starter R.V. DEURSEN
  • Start date Start date
R

R.V. DEURSEN

I have a tab delimited .txt file for import in Excel.
After import it has empty rows between every row.
Is it possible automaticely import the text rows and skip
the empty ones during import in excel
(in VBA)

thank you so much
 
R.V. DEURSEN said:
I have a tab delimited .txt file for import in Excel.
After import it has empty rows between every row.
Is it possible automaticely import the text rows and skip
the empty ones during import in excel
(in VBA)
Click to expand...

I doubt it. This could happen if the application generating these .TXT files
were screwing up the newline characters. Specifically, under Windows this is
what Excel does if the ASCII carridge return (ASCII decimal character code
13) comes before the ASCII linefeed (ASCII decimal character code 10).

Where are these .TXT files coming from?

Anyway, there's a relatively simple way to fix this. If the data from the
..TXT file filled A1:Z200 with every other row empty, select AA1:AA200 with
AA1 the active cell, type the formula

=IF(COUNTA(A1:Z1),ROW(),1E10)

hold down a [Ctrl] key and press the [Enter] key. Select A1:AA200. Sort on
column AA in ascending order. Then clear column AA.
 
You could also simply use the following code after export:

Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

It deletes all the rows in which the cells in column A are blank.

Good Luck !


Harlan Grove said:
R.V. DEURSEN said:
I have a tab delimited .txt file for import in Excel.
After import it has empty rows between every row.
Is it possible automaticely import the text rows and skip
the empty ones during import in excel
(in VBA)
Click to expand...

I doubt it. This could happen if the application generating these .TXT files
were screwing up the newline characters. Specifically, under Windows this is
what Excel does if the ASCII carridge return (ASCII decimal character code
13) comes before the ASCII linefeed (ASCII decimal character code 10).

Where are these .TXT files coming from?

Anyway, there's a relatively simple way to fix this. If the data from the
.TXT file filled A1:Z200 with every other row empty, select AA1:AA200 with
AA1 the active cell, type the formula

=IF(COUNTA(A1:Z1),ROW(),1E10)

hold down a [Ctrl] key and press the [Enter] key. Select A1:AA200. Sort on
column AA in ascending order. Then clear column AA.
Click to expand...
 
Works perfectly.

Thank you so much
-----Original Message-----
You could also simply use the following code after export:

Range("A:A").SpecialCells (xlCellTypeBlanks).EntireRow.Delete

It deletes all the rows in which the cells in column A are blank.

Good Luck !


Harlan Grove said:
I doubt it. This could happen if the application
Click to expand...
generating these .TXT
files
were screwing up the newline characters. Specifically,
Click to expand...
under Windows this
is
what Excel does if the ASCII carridge return (ASCII decimal character code
13) comes before the ASCII linefeed (ASCII decimal character code 10).

Where are these .TXT files coming from?

Anyway, there's a relatively simple way to fix this. If the data from the
.TXT file filled A1:Z200 with every other row empty, select AA1:AA200 with
AA1 the active cell, type the formula

=IF(COUNTA(A1:Z1),ROW(),1E10)

hold down a [Ctrl] key and press the [Enter] key. Select A1:AA200. Sort on
column AA in ascending order. Then clear column AA.
Click to expand...


.
Click to expand...
 
You could also simply use the following code after export:

Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

It deletes all the rows in which the cells in column A are blank.
Click to expand...
...

True, but simple interactive approaches are also handy. My sorting approach
isn't optimal. Better would be inserting a row (a blank row) above the .TXT file
data, selecting the entire worksheet, running Data > Filter > AutoFilter,
selecting (Blank) in the column A dropdown, pressing [Shift]+[SpaceBar] to
select entire rows, then Edit > Delete to delete the rows in which column A is
blank.

Note that the approach above isn't general. If CSV or any other form of
structured text files with delimited fields had empty first fields but nonempty
subsequent fields, this approach would delete such records/rows. The sorting
approach I suggested guards against that possibility.
 
Back
Top