Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2004
    Posts
    21

    Unanswered: Delete the entire row is the cell in column A is blank

    Hi there

    I have tried various methods but still can't come up with the solution.I need to write a code which finds blank cells in Column A of an Excel worksheet. And when there is a blank cell in column A, the entire row will be deleted and the rows below it will shift up to occupy that row.The number of rows in column A is not fixed and it will vary from one worksheet to another worksheet.Pls help.Thanks

    David

  2. #2
    Join Date
    Mar 2004
    Location
    Minnesota, USA, Earth
    Posts
    65

    You could try this...

    You could try this...(off the top of my head)


    Sub DeleteBlankRows()

    Range("A1").select
    Do
    If activecell = "" Then
    activecell.entirerow.delete shift:=xlUp
    activecell.offset(-1,0).select
    End If
    activecell.offset(1,0).select
    Loop until....(insert some test for the end of the file).

    End Sub

  3. #3
    Join Date
    Feb 2004
    Posts
    21

    Unsure how to determine last row

    Hi SR22Mike

    The problem I had was to determine the last row in column A as this changes from one worksheet to another.So I am not sure how to to do the loop command.

  4. #4
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    Sub DeleteRowsWithBlanks()
    Dim lastrow As Long
    lastrow = Range("A:A").SpecialCells(xlLastCell).Row
    Range(Cells(1, 1), Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
    End Sub

    'This is a good example of the dreadful performance of VBA loops.
    'To test, put both of the following into a new workbook, then run each.

    Sub DeleteRowsWithBlanks()
    Dim lastrow As Long
    ActiveWorkbook.Sheets.Add
    Range("A1").Value = "foo"
    Range("A5000").Value = "bar"
    Start = Now()
    lastrow = Range("A:A").SpecialCells(xlLastCell).Row
    Range(Cells(1, 1), Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
    Cells(1, 2).Value = Now() - Start
    Cells(1, 2).NumberFormat = "ss.sss ""seconds"""
    End Sub


    Sub DeleteBlankRows()
    Dim lastrow As Long
    Dim i As Long
    ActiveWorkbook.Sheets.Add
    Range("A1").Value = "foo"
    Range("A5000").Value = "bar"
    Start = Now()
    lastrow = Range("A:A").SpecialCells(xlLastCell).Row
    Range("A1").Select
    i = 1
    Do While i < lastrow
    If ActiveCell = "" Then
    ActiveCell.EntireRow.Delete shift:=xlUp
    ActiveCell.Offset(-1, 0).Select
    End If
    ActiveCell.Offset(1, 0).Select
    i = i + 1
    Loop
    Cells(1, 2).Value = Now() - Start
    Cells(1, 2).NumberFormat = "ss.sss ""seconds"""
    End Sub
    Last edited by actuary; 05-16-04 at 12:07.

  5. #5
    Join Date
    Feb 2004
    Posts
    21

    Tried a code but too slow

    Hi actuary

    I have a worksheet with about 30,000 rows and I tried this code :
    Sub DeleteBlankRows2()
    Dim lastrow As Long
    Dim i As Long
    lastrow = Range("A:A").SpecialCells(xlLastCell).Row
    Range("A1").Select
    i = 1
    Do While i < lastrow
    If ActiveCell = "" Then
    ActiveCell.EntireRow.Delete shift:=xlUp
    ActiveCell.Offset(-1, 0).Select
    End If
    ActiveCell.Offset(1, 0).Select
    i = i + 1
    Loop
    End Sub

    It took quite a while to do the code. I tried the code that you have given meub DeleteRowsWithBlanks()
    Dim lastrow As Long
    lastrow = Range("A:A").SpecialCells(xlLastCell).Row
    Range(Cells(1, 1), Cells(lastrow, 1)).SpecialCells(xlCellTypeBlanks).EntireRow.Delet e
    End Sub

    But the whole worksheet turn out blank. Is there a problem with it or is there a faster way other than the first code? Thanks

  6. #6
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    My code is just a VBA version of what can be done with Excel's native functionality. Try doing it manually on the original worksheet: Use your mouse to select all the cells in column A all the way to the bottom of your data. Then choose Edit, Go To..., Special..., Blanks. Then choose Edit, Delete..., Entire row.

    If the above doesn't work correctly, then it's a problem with the worksheet, not the code. You'll need to zip it and post it here.

  7. #7
    Join Date
    Oct 2003
    Posts
    1,091
    Here is a shrotened version that allows you to choose the column upon which to base the deletion.
    Code:
    Sub DeleteEmptyRowsMain()
    'from Mr. Excel.com which allows user to
    'choose the column by selecting it.
    Dim myColm As Range
        Set myColm = Application.InputBox("Choose column(s) to clear", Type:=8)
        On Error Resume Next
        myColm.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
    End Sub
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  8. #8
    Join Date
    Feb 2004
    Posts
    21

    Set Column as Column A

    Hi shades

    Thanks for the code.It works perfectly.But if I want to fix the column as the first column(eg:Column A),what value do I set myColm as?Thanks

  9. #9
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    If you want to operate on the whole column anyway, just use:

    Code:
    Sub DeleteRowsWithBlanks()
    Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End Sub
    'This seems to be just as fast as selecting a limited number of rows.
    Last edited by actuary; 05-17-04 at 11:12.

  10. #10
    Join Date
    Feb 2004
    Posts
    21

    Puzzled

    Hi there

    I tried the code already but this time with 2 different files. One file that has 10 rows and the code works perfectly.Another worksheet with 30,000 rows but the result with the one with the 30,000 rows is a blank worksheet. Is there a limitation or something like that? Thanks

  11. #11
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    I don't know, but if you'll post the spreadsheet we can find out.

    Have you tried it manually like I asked? What happened?

    Are you absolutely sure the macro produces a blank sheet on the 30,000 row version? In other words, did you press CTRL+Home to go to the top of the sheet and still you saw no rows?
    Last edited by actuary; 05-17-04 at 16:21.

  12. #12
    Join Date
    Oct 2003
    Posts
    1,091
    Quote Originally Posted by davidkoh79
    Hi shades

    Thanks for the code.It works perfectly.But if I want to fix the column as the first column(eg:Column A),what value do I set myColm as?Thanks
    The InputBox allows you to select the Column. So move the mouse over Column A heading and click.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  13. #13
    Join Date
    Feb 2004
    Posts
    21
    Hi actuary

    I tried the code on the 30,000 row worksheet and it turned out blank. My 30,000 row worksheet has data from columns A to Y.And it is filled also from row 1 to row 30,000. But in column A each alternate row is blank .

  14. #14
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    I give up. Post the spreadsheet or don't post at all.

  15. #15
    Join Date
    Feb 2004
    Posts
    21

    Sorry

    Hi actuary

    Sorry. I have uploaded the file. Pls take a look. Thanks
    Attached Files Attached Files

Posting Permissions

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