Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Delete Empty Row Macro Works Sometimes

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have the following macro to delete empty rows and it works fine, but for some weird reason it won't work when I copy paste special all the data then I edit, replace anything with 12:00:00 am, with nothing I leave the field blank , which leaves the rows blank.

    Any ideas or suggestions, Thanks.

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


  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The problem is the macro ? it seems just fine. Do you select everything, copy, paste as values, then replace "anything" with that hour and leave the empty cells like that ?

    You should try a COUNTA in Excel in an entire row to see what you get, maybe they just "look" empty and that's why it isn't deleting them.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I did the counta thing and I got a result of number 2. I went into each and it blank, how could I find what cell it is in?

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, I used counta on each cell and found out what was in there, 3 spaces. I don't know how they got there, I used edit replace to remove them and it did, except. In another cell, a 1 still appeared but no spaces. I selected it and it went away. Any suggestions? Thanks

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •