Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    delete blank cells (excel97+)

    I have a long column of data which includes blank/empty cells -I want to delete the empty cell and move the cells up
    The following macro does this but does not remove consecutive empty cells so I have to run the macro many times----I understand the problem but dont know how to correct it!!

    Sub delete_blanks()
    Dim oCell As Range
    For Each oCell In Worksheets("sheet1").Range("a1:a150")
    If oCell.Value = "" Then
    oCell.Delete
    End If
    Next oCell
    End Sub
    Thanx
    Smbs

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete blank cells (excel97+)

    Two non-VBA solutions are:
    1. Sort your column. Blanks will go to the bottom.

    2. Select the range, press Cntrl-G, Click on the Special Button, select "Blanks" and OK. Then right click on a selected cell and choose delete (shift cells up)

    3. a VBA solution would have you looping in reverse order. e.g...
    dim i as integer
    for i = 150 to 1 step -1
    if range("A" & i).value=""
    range("A" & i).delete
    end if
    next i

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete blank cells (excel97+)

    This should do what you want:

    <pre>Public Sub DelBlank()
    Dim lLastRow As Long, I As Long
    lLastRow = Cells(Application.Rows.Count, Columns("A").Column).End(xlUp).Row - 1
    For I = lLastRow To 0 Step -1
    If Range("A1").Offset(I, 0) = "" Then
    Range("A1").Offset(I, 0).Delete
    End If
    Next I
    End Sub
    </pre>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: delete blank cells (excel97+)

    Great thanx for replies
    Smbs

Posting Permissions

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