SKIP EMPTY ROWS FROM IMPORT
 

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

SKIP EMPTY ROWS FROM IMPORT
 

"R.V. DEURSEN" wrote...
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)

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.

SKIP EMPTY ROWS FROM IMPORT
 

You could also simply use the following code after export:

Range("A:A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete

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

Good Luck !


"Harlan Grove" wrote in message
...
"R.V. DEURSEN" wrote...
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)

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.

SKIP EMPTY ROWS FROM IMPORT
 

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" wrote in message
...
"R.V. DEURSEN"
wrote...
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)

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.




.

SKIP EMPTY ROWS FROM IMPORT
 

"Fred" wrote...
You could also simply use the following code after export:

Range("A:A").SpecialCells(xlCellTypeBlanks).Entir eRow.Delete

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

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.

--
To top-post is human, to bottom-post and snip is sublime.