Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want the autofill/drag function to skip empty cells | Excel Discussion (Misc queries) | |||
paste special / skip empty cells doesn't work!? | Excel Worksheet Functions | |||
Formula returns empty; chart plots zero; I want to skip | Charts and Charting in Excel | |||
How to skip empty cells while running a macro? | Excel Programming | |||
Skip empty and text cells | Excel Programming |