MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds


  Asked By: Brayden    Date: Sep 13    Category: MS Office    Views: 625

I have this procedure to delete empty rows:

Sub DeleteEmptyRows()
Dim LastRow As Long
Dim r As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
LastRow = LastRow + ActiveSheet.UsedRange.Row - 1
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = 0 Then
Next r
End Sub

It works perfectly UNTIL I paste new information over the old
information and try to run it again. It doesn't delete the empty
rows anymore.

I discovered why not by using the ISBLANK formula on the rows I just
pasted in onto the active sheet. Even though the cells are visibly
empty (the cell and formular bar), the ISBLANK formula returns
FALSE. I'm sure that is why the above procedure is not deleting the
empty rows anymore. Because technically there aren't any empty rows.

If I hit 'clear contents' on the visibly empty rows, then the
ISBLANK formula returns TRUE and the above procedure works again.
Because now the visibly empty rows are truly empty.

The problem is that my worksheet has about 3,000 rows and
hitting 'clear contents' on all the empty rows to get the above
procedure to work just isn't worth it.

Has anyone come across this? And do you have a solution? Or an idea?



No Answers Found. Be the First, To Post Answer.

Didn't find what you were looking for? Find more on DeleteEmptyRows Or get search suggestion and latest updates.

Related Topics:



Related Post