Delete all blank records within data set

Hello!

I have a table that can have up to 65000 records in it. The issue I have is there are a ton of BLANK records. What I want is to have some code that will delete ALL the blank records within the data set. The number of columns is from A:AM . Please let me know if you have any questions or need any additional information.

Thanks!

It isn't clear how these blank records are located and in which way you want to delete them. Example1: The blank cells could be in any column and any row; or the blank cells are only in column E but could be located in any row. Example2: I need to delete the entire row if one cell is blank; or I only need to delete the cell and shift everything else up if it is empty.

Please specify what your requirements are.

try this

Please Login or Register  to view this content.

DESCRIPTION
If cells in first column (Cells(a,1)) is empty then delete row. If you want to look at second col, change the code from Cells(a,1) to Cells(a,2). Likewise, if you want first col and second col, IsEmpty(Cells(A, 1)) And IsEmpty(Cells(A, 2))
It defines var as used rows in spreadsheet, and updates var everytime it does a loop.

Hey a8015945,


That almost worked. I think the code deleted a record even if there was one cell in the record that was blank. So hopefully this will clarify for you as well as everyone else that had replied to the post.
1) The data set contains columns A through AM. If one of the columns within the record, let's say B10 (row 10) is blank, I do NOT want this record deleted. I ONLY want the the ENTIRE ROW to be deleted if each cell within the row is blank. So if A10 through AM10 (each cell is blank) then delete row 10.
2) I do not want to have to do any manual work (i.e. filters)

Thank you for all your help! Please let me know if there are any other questions that i can answer.

Ok let me have another look at the loop.

try this one now then

Please Login or Register  to view this content.

DESCRIPTION.
Slight change from previous code. Now the macro looks at each cell in the row. If the cell is NOT empty (e.g. has a value) then the macro looks at the next row. For each EMPTY cell in the row, x is incremented. If x=total number of columns, then the row must be EMPTY, therefore Rows(r).Delete

NOTE 1
Macros dont update themselves, they have to be started manually. You can do this by adding an autoshape, then right click and ASSIGN MACRO.

NOTE 2
Deleting rows in this fashion may take some time, depending on number of empty rows, total number of rows etc. You may need to allow anywhere upto 5 minutes depending on the situation.

Marcol I have spotted a mistake in your code.

ORIGINAL
If WorksheetFunction.CountA(Range("A" & RowNo & ":AM" & RowNo)) = 0 Then Cells(1, RowNo).EntireRow.Delete

UPDATED
If WorksheetFunction.CountA(Range("A" & RowNo & ":AM" & RowNo)) = 0 Then Cells(RowNo,1).EntireRow.Delete

When I tired your original on a dummy spreadsheet, it deleted the majority of the data, when I made the change it did exactly what it should do.

P.S. By the way, thanks for sharing this method of coding. As you can probably guess, i'm reletavely new to programming macros (self taught over 4 months) so I often aim for something that just gets results, rather than aiming for effieciency atm. And to top things off, my dad is from Fife, small world

hmm...

whatever happened to the filter?

here's another to try. should work fine with 65,000 rows

Please Login or Register  to view this content.