+ Reply to Thread
Results 1 to 5 of 5

still have empty cell after run Delete empty cells code

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    818

    still have empty cell after run Delete empty cells code

    I have to run few time to delete all empty cell if Range A5:A20 are empty. Please help me
    Dim lastrow As Long
    Dim iCell As Long
    
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    
    For iCell = 1 To lastrow
        If Range("A" & iCell) = "" Then
            Range("A" & iCell).Delete Shift:=xlUp
        End If
    Next iCell
    Regards,
    tt3
    Last edited by tuongtu3; 11-25-2012 at 04:28 PM. Reason: Solved

  2. #2
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: still have empty cell after run Delete empty cells code

    Go backwards:

    Sub hfdjs()
    
    Dim lastrow As Long
    Dim iCell As Long
    
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    
    For iCell = lastrow To 1 Step -1
        If Range("A" & iCell) = "" Then
            Range("A" & iCell).Delete Shift:=xlUp
        End If
    Next iCell
    End Sub
    Gary's Student

  3. #3
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    818

    Re: still have empty cell after run Delete empty cells code

    Quote Originally Posted by Jakobshavn View Post
    Go backwards:

    Sub hfdjs()
    
    Dim lastrow As Long
    Dim iCell As Long
    
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    
    For iCell = lastrow To 1 Step -1
        If Range("A" & iCell) = "" Then
            Range("A" & iCell).Delete Shift:=xlUp
        End If
    Next iCell
    End Sub
    Hi Jacobshavn,
    Thank you very much for your help and can you explain what does "Step -1" meaning/doing in this code?

    Regards,
    tt3

  4. #4
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: still have empty cell after run Delete empty cells code

    Sure..........

    For I=1 to 100 Step 1
    means I=1,2,3,4,...........100

    For I=100 to 1 Step -1
    means i=100,99,98,97...............1

  5. #5
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    818

    Re: still have empty cell after run Delete empty cells code

    Quote Originally Posted by Jakobshavn View Post
    Sure..........

    For I=1 to 100 Step 1
    means I=1,2,3,4,...........100

    For I=100 to 1 Step -1
    means i=100,99,98,97...............1
    Hi Jakobshavn,
    I understand and thank you very much for your explaination.

    Regards,
    tt3

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1