+ Reply to Thread
Results 1 to 10 of 10

Delete Row

  1. #1
    Registered User
    Join Date
    11-10-2006
    Posts
    20

    Delete Row

    Hi

    I want to check a worksheet where column C has figures entered. How do I check this column and if it is blank or has a 0 (zero) entered in it, the row is deleted. The range is A9:E119 for the whole work sheet and the Column range is C9:C119.

    I have tried to write it mylef but I just dont know enough yet.

  2. #2
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    Hi just add a module and paste this in, you can either run it via a command button or give it a keyboard shortcut

    Please Login or Register  to view this content.
    Regards,
    Simon

  3. #3
    Registered User
    Join Date
    11-10-2006
    Posts
    20
    Hi Simon

    Many thanks for the help, when I run the code I get this error:

    Delete method of Range class failed

    and when I press debug it highlights this line:

    MyCell.EntireRow.Delete

    Regards

    Alan

  4. #4
    Forum Contributor
    Join Date
    08-19-2006
    Posts
    116
    Hi Alan

    And what do you think of this?

    Sub Delete()
    For i = 9 To 119
    For j = 3 To 3
    If Cells(i, j) = "0" Or Cells(i, j) = "" Then
    Rows(i).Delete
    End If
    Next j
    Next i
    End Sub
    Last edited by Raiby; 11-17-2006 at 01:48 PM.

  5. #5
    Registered User
    Join Date
    11-10-2006
    Posts
    20
    Hi Raiby

    Thanks for the code, I cannot get it to work.

    Alan

  6. #6
    Forum Contributor
    Join Date
    08-19-2006
    Posts
    116
    Hi Alan

    Did you see my correction?

  7. #7
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161
    DrumsforMonty, i'm no expert and my code worked for me but there is a flaw, as it looks from the top down if it comes across 2 blanks in a row it will only see them as one, so i posted my answer to another forum (full of experienced guys) and i got a lesson aswell as a couple of solutions here's the thread http://vbaexpress.com/forum/showthre...9882#post79882 MDmackillop probably has the solution closest to what you need.

    Regards,
    Simon

  8. #8
    Registered User
    Join Date
    11-10-2006
    Posts
    20
    Hi Simon, thanks for that, I have joined that forum.

  9. #9
    Registered User
    Join Date
    11-10-2006
    Posts
    20
    Hi Raiby

    Sorry I didnt see a correction.

    Alan

  10. #10
    Forum Contributor
    Join Date
    08-19-2006
    Posts
    116
    Hi Alan

    In fact I forgot this piece of code:
    Or Cells(i, j) = "" for the blanks

    But i suppose that it doesn't change your problem.It's strange. The code works for me.

+ 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