• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Delete empty rows from imported text using formulas

lukevba

New Member
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
 

Attachments

  • question.xlsx
    18.9 KB · Views: 5
Last edited:

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 ! »
 
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...
 

Attachments

  • Delete empty Row on a selected column.xlsm
    113.2 KB · Views: 2
Back
Top