Upsetting issue concerning "Blank Row Deletion"

[Fabulist]

Hello everyone,

I am working on an Excel file with numerical data, of 2825 rows total. Every about two data rows, there is a blank row, and every about 50 rows, there is a total row. What I need to do, is to remove the totals and blank rows so I can couple this file with 18 others that range from 1000 to 25000 rows and filter them accordingly.

On all other files, I have followed the first method here: 4 Easy Ways to Delete Blank Rows in Excel to delete empty rows including total rows, since total rows have blank cells on their row, they are automatically selected and deleted, which better serves my purpose. On all other files, this method proved successful, my debit and credit number totals agree.

However, on one file, whenever I delete the empty rows, my totals do not agree. Respectively, if I go through the file in order to delete all intermediate row totals manually, and leave all blank rows unchanged, the debit and credit totals agree. If I do this first and then delete all blank rows, the totals are correct.

All intermediate totals are colored, so the lines which are being selected for deletion are clear and they seem correct, hence there are no leftovers from the intermediate totals. When “blanks” are selected, I select that the whole row is deleted, and not just the cell.

How can I fix this headache, and is there a chance all my other files have problems as well even if my debit and credit totals agree? Maybe there is a better way to do this?

Thanks to anyone who responds in advance.

 

[My Aswer Is This]

Show us the code you are now using.

 

[Fabulist]

What code?

What I am currently doing is grabbing the data from all the files from the beginning, filtering everything including the blank and depleted rows, and I delete them manually. Currently, I have no issues with this method, everything seems to agree. However, I would still like to know why the automatic blank row deletion method went wrong.

 

[My Aswer Is This]

What range of data are we looking at to see if the row is blank.
For example:
I can write us a Vb script that will do this.
Is there one particular column to look at?
For example: If Row(1) Column (B) is empty then delete row.

 

[Myconservator]

Actually the link that OP (Original Poster) supplied already has a VBA code as the 4th way.

To me it seems like OP is trying to go through their bank account statement or something similar.

Now can OP try to see if they can delete the totals and before deleting blank cells see if the column totals of debits and credits match?

 

[Fabulist]

@ My Aswer Is This

No, there is no specific blank row placement, blank and sub-totals appear regularly, but not consistently. That is why I tried to select all empty cells and delete their rows. However, it also deletes rows with data for a reason.

@ Myconservator

I am going through massive company files with financial information, their software extracts the data to Excel in an unorderly manner. I already tested by deleting subtotals and the blank rows manually via filters, it works. But automatic blank row selection should work as well, but it does not.

 

[Myconservator]

I totally understand.
In this particular file that you are having problem test this theory out.

First check the column total for credits and debits before you do anything, see if they match.
Second delete the sub totals manually and check if credits and debits column totals still match.

 

[Fabulist]

As I said in my OP, I already did that and they do match. Only when I delete through highlighting they do not.

 

[Myconservator]

One more thing and I won't bother you any more.

Insert a column at the beginning of your data.
Enter formula as sum of debit and credit column.
Copy that column and paste it back as value only.

Than select that column as well when you are deleting blank cells.
See if that fixes your problem.