Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Deleting rows not containing data. HOW?

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    For Gary Hewitt-Long,

    To try and help Roni on, I've had a look at the spreadsheet in question.

    What he needs is for the macro to test each row, along the range C4:L4 to see if any data is present. If nothing, then delete the row. If any data is present in any cell then the row remains. The macro then needs to carry on testing up to C290:L290

    I've amended your VB as follows and run it:
    Sub deleteBlankRows()

    Range("C4:L4").Select ' Select the starting cell
    For i = 4 To 290 ' Change 100 to whatever the last row you want to iterate to.

    If ActiveCell.Value Like "" Then
    ActiveCell.EntireRow.Select
    Selection.Delete Shift:=xlUp
    ActiveCell.Offset(RowOffset:=1).Activate

    Else

    ActiveCell.Offset(RowOffset:=1).Select

    End If

    Next i
    Range("C290:L290").Select

    End Sub

    Whilst it has deleted approx 185 blank rows, it has still left some 90 rows which I know have no data in them.

    Knowing nothing about VB, I'm stuck - can you dig me out of it?

    Cheers,

    Nobby


  2. #12
    Board Regular
    Join Date
    Feb 2002
    Location
    United Kingdom
    Posts
    68
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Re the above posting, I've run another couple of tests on the code and it has also deleted rows where data definitely exists.

    Could it be anything to do with the use of "" for testing for a blank cell i.e. is this looking for no text rather than no data at all? Dunno, just guessing

    Nobby

  3. #13
    Guest

    Default

    I've used the last code and it's not working.
    The first two columns have TEXT in each row, but I only want the macro to look at columns C-L for NUMBERS, if no numbers, delete ENTIRE row (including TEXT in first two columns).

    Also, it should ignore the formula in Column M for each row (adding across each row). There are "0.00" in each box if there isn't a number in columns C-L. I don't want those 0.00's to count as NUMBERS in the macro.

    HELP. Thanks again.

  4. #14
    Guest

    Default

    ACtually, my last post about trying the prior code was in reference to the code prior to that ( hadn't seen the last one yet). But it sounds like that isn't working either. HELP some more.

    Thanks

  5. #15
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hello, I'm assuming your data is in the range C4:L104. If so, you may want to try the following macro. Just paste it as described earlier and run it.

    Sub Crunch()
    myrng = ActiveCell.Address
    Application.ScreenUpdating = False
    [m4] = "=sum(l4:c4)"
    [m4].Copy
    [m4:m104].Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    For Each cell In Selection
    If cell.Value = "0" Then
    cell.ClearContents
    End If
    Next cell
    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Selection.ClearContents
    Range(myrng).Select
    Application.ScreenUpdating = True
    End Sub

    HTH. Cheers, Nate

    [ This Message was edited by: NateO on 2002-02-28 10:26 ]

  6. #16
    Guest

    Default

    Almost perfect (and thank you), however
    when I run the macro, I get an error screen "Run time error "1004" Cannot change part of merged cell" to which I click END and then OK to same "merged cell" error message. I searched thru my spreadsheet, I can't find any merged cells. Any ideas.

    Also, sometimes (and I haven't found a pattern yet) it doesn't ALWAYS delete all the blank rows. But if I go back and change the row number to new row number and run it again, it takes out the remaining empty rows???)

  7. #17
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-28 10:59, Anonymous wrote:
    Almost perfect (and thank you), however
    when I run the macro, I get an error screen "Run time error "1004" Cannot change part of merged cell" to which I click END and then OK to same "merged cell" error message. I searched thru my spreadsheet, I can't find any merged cells. Any ideas.

    Also, sometimes (and I haven't found a pattern yet) it doesn't ALWAYS delete all the blank rows. But if I go back and change the row number to new row number and run it again, it takes out the remaining empty rows???)
    If you shouldn't have ANY merged cells in the spreadheet, select the whole sheet then right click, select Format cells, Alignment and uncheck Merge Cells.

    The code I put up did check for "" i.e. nothing there and iterated through column A from the starting point you tell it, untill it has iterated the number of times you put in the code.

    Someone mentioned it left cells undeleted, yep it must have done looking at it again, sorry.

    The reason it did that was that when it deleted the row it shifted the cells up and THEN moved down a row. Trouble is the cell it moved into imeadiately after deleting the row was the one it should have been testing next, therefore it didn't get tested. Whoops, sorry.

    The alternative would be to use xlDown (but I haven't tested it! )


    Regards,

    Gary Hewitt-Long

  8. #18
    Board Regular
    Join Date
    Apr 2002
    Location
    Cape Town,South Africa
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try Autofiltering the selection,and see if that solves your problem my friend.



  9. #19
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Come on Gareth,

    this post was over 2 months old till you dredged it back up to the top of the list !!!!

    Your suggestion of "autofilter" had already been suggested by Jack anyhow, 2 months ago

    Full marks for looking through older entries though, it's amazing what gems are hidden away in the archives

    Chris

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
  •