Delete empty rows from imported text using formulas

[lukevba]

Hello everyone,

I have an Excel problem, I am supposed to:
- import a text which lacks certain rows from.txt file
- clean it - change some text/letter combinations to letters etc, vlookup some other values
- delete empty rows using formulas

Everything should be done automatically, so when a new text file is imported I can get my new, neat database without having to repeat all those steps again. This sounds easy to do using VBA, but... here is the catch - I can't use VBA for that.

I have managed to create an array formula which does that, It's been done in 2 different approaches:
- delete a row if at least one cell in that row is empty
- delete a row if every cell in that row is empty

Formula works fine, but it's very CPU consuming - my original database has 10 columns and around 25000 rows so imagine using that formula 250 000 times.

My question is - how can I simplify the formula so it would delete blank rows of a certain cell range without consuming 100% of a CPU for 20 minutes and almost crashing excel.

I am attaching an excel file with my approach.

I hope we can figure something out together!

Regards,
lukevba

 

[Marc L]

Hi !

When I was called to achieve similar projects
without a VBA or a VBScript code, I just asked for a clean source file …

After a while, often the answer was : « Ok, do it like you want ! »
​

 

[nadjalil]

Hi
check the attached file if this VBA Script is working for you. what you need to do, Select the column where you want to remove the empty cells and run this macro it'll delete the entire row which has an empty cell on selected column...

i hope it'll work...