Automatically delete empty rows

[applebanana]

Hello,

I have document with 5000 rows and I am looking for a method to automatically delete rows that are empty. I need to have one range of columns that include the empty rows and another range of columns without the empty rows. It is easy to remove empty rows using e.g. a helper cell and using the filter option but I cannot do any work manually, this is a data generating document so it needs to be done automatically.

I have found a formula that does exactly as I want but the problem is that it requires so much workload that excel becomes slow and pretty much unusable. Is there any other method to do this automatically that doesn't slow down excel?

Please find attached my document (reduced to 800 rows because of size). Thank you

[Roel Jongman]

you say this is a data generating document, how is the data generated? and could you not control the generation proces to create one sheet to write every line with empty rows and one sheet to only write rows with values?

[Logit]

.
The rows that appear to be empty are in fact, not empty. At least Excel doesn't view them as blank.

Using your workbook, I tried several different macros to delete the "blank" rows. Nothing was deleted with any of the different macros.

I tried using the macros on one of my workbooks (a copy) with 300,000 thousand rows of data, including blank rows interspersed within those 300,000 rows. The blank rows were eliminated
in less than 2 seconds.

Here is one of the macros that clear blank rows in my workbook :

Please Login or Register  to view this content.

[applebanana]

Yes, I should have phrased it differently; this sheet is part of a data generating process and this sheet simply converts the data to include only non-empty rows. The data in the first column range are not empty if the condition in column AQ is met, that is: =IF(AQ=1,A2,""), etc. It's an easy job manually but I cannot come up with a formula to do it automatically. Basically it should just check if a row contains 1 in column AQ, decide whether or not to include it, then move to the next row, and so on.

[Logit]

.

if a row contains 1 in column AQ, decide whether or not to include it

What is the decision mechanism for the above ?

You initially said you wanted to delete all blank rows ... if you are only focusing on the rows (AQ) that have a numeral "1" ... what are you gonna do with the blank rows that don't have a "1" ?


Deciding what you want to do and possibly how to go about it is ALOT easier for you, because you understand what your project does and what the ultimate goal is. You are not helping others to
assist you because your descriptions/questions are counter intuitive and confusing.

Please be specific what it is you want and what the end result will be. Oftentimes, manually creating (and posting here) a worksheet with a "before" and "after" view of data to demonstrate what it will look like can be
priceless to assist with your written explanation.

[Fluff13]

This will delete all rows where col A is blank

Please Login or Register  to view this content.

[applebanana]

I did upload exactly what I wanted my sheet to look like, with the first column range containing the empty cells (which are referred to by another document, in which the empty cells are replaced) and the second column range not containing the empty cells. This is the end result that I need, and the formula does that job perfectly. The only problem is that by using this formula the workload is too much for excel to handle, as this is for 5000 rows.

[JeteMc]

This may speed up your process.
In cell AU2 modify the formula to read: =IF($AT2="","",INDEX(B$2:B$801,MATCH(ROW()-1,$AR$2:$AR$801,0)))
1) Press Ctrl + Shift + down arrow and then Ctrl + d to copy the formula down to AU801
2) Press Ctrl + Shift + right arrow and then Ctrl + r to copy the formula over to CH2:CH801
Let us know if you have any questions.

[applebanana]

This may speed up your process.
In cell AU2 modify the formula to read: =IF($AT2="","",INDEX(B$2:B$801,MATCH(ROW()-1,$AR$2:$AR$801,0)))
1) Press Ctrl + Shift + down arrow and then Ctrl + d to copy the formula down to AU801
2) Press Ctrl + Shift + right arrow and then Ctrl + r to copy the formula over to CH2:CH801
Let us know if you have any questions.

Thank you for your help

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.