Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts

    deleting rows using excel macro - repost

    Hi
    I have a set of data, where I want to scroll through the rows, and delete the ones that have the word "BlanK" in column C. I would also like to stop when a cell in column C is empty.The data are imported from a .csv file and are in General format. A newbie, I am still not fully versed in the VBA syntax and I have tried a couple of things but can't seem to get it to work correctly. It seems to me that this should be rather simple but I have already blown a couple of hours on this. Any suggestions would be greatly appreciated.

    I tried this but it doesn't seem to recognize "Blank" or Blank. There also may be an issue when a row is deleted (the old active cell is now the new active cell without being offset. I am using Excel 2007.

    Sub DeleteRows()

    Range("c5").Activate

    Do While ActiveCell.Value <> Empty
    If ActiveCell.Value = "Blank" Then
    Rows(ActiveCell.Row).Delete
    Else
    ActiveCell = ActiveCell.Offset(1, 0).Select
    End If

    ActiveCell = ActiveCell.Offset(1, 0).Select
    Loop



    End Sub

  2. #2
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    When deleting items from a collection, the general rule is to delete from the end back to the beginning. This modified version of your existing code, however does delete without problems...
    '---
    Sub DeleteRows()
    Range("C5").Activate

    Do While Not IsEmpty(ActiveCell)
    If ActiveCell.Value = "Blank" Then
    ActiveCell.EntireRow.Delete
    End If
    ActiveCell.Offset(1, 0).Select
    Loop
    End Sub
    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (Extras for Excel add-in: convenience built-in)

  3. #3
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Thanks for the help

    Sub DeleteRows()
    Jim
    Thanks so much for the help. The code you sent works fine as long as there are not consecutive "Blank" rows. In that case it deletes the first row and skips the second row. I added one line of code to back up the active cell if a row is deleted. That seems to do the trick.
    Thanks very much for the help. I can see this will be a long learning curve!
    Arjay13



    Range("C5").Activate

    Do While Not IsEmpty(ActiveCell)
    If ActiveCell.Value = "Blank" Then
    ActiveCell.EntireRow.Delete
    ActiveCell.Offset(-1, 0).Select
    End If

    ActiveCell.Offset(1, 0).Select

    Loop
    End Sub

  4. #4
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts
    You might to also try this flavor...

    Range("C5").Activate
    Do While Not IsEmpty(ActiveCell)
    If ActiveCell.Value = "Blank" Then
    ActiveCell.EntireRow.Delete
    Else
    ActiveCell.Offset(1, 0).Select
    End If
    Loop

  5. #5
    Lounger
    Join Date
    Feb 2011
    Posts
    27
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Ah Yes. In the back of my tiny mind, I was thinking about an Else...nicely done

Posting Permissions

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