Results 1 to 8 of 8

Thread: Automatically deleting values

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2012
    Posts
    4

    Automatically deleting values

    Hi Everyone I wonder if someone can help me.

    On excel I want to automatically delete all rows if both columns N and O have the value 0.
    Then I would like colomns A B D G J K L M P Q deleted.

    Which is the best way to approach this?

    Many thanks in advance

  2. #2
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: Automatically deleting values

    welcome to the forum,
    it's better post what u hav tried so far, anyway try something like this
    Code:
    Dim i As Long, DelRng As Range
    For i = 1 To 100
        If Cells(i, "N").Value = 0 And Cells(i, "O").Value = 0 Then
            Set DelRng = Union(Cells(i, "A"), Cells(i, "B"), Cells(i, "D")) 'add ur cells
            DelRng.ClearContents
        End If
    Next
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  3. #3

    Thread Starter
    New Member
    Join Date
    May 2012
    Posts
    4

    Re: Automatically deleting values

    Thanks for your post,
    I have tried this code but it doesn't work.
    The contents of the colums are cleared but the headings are not.
    Also the rows N and O still have have the values of 0 in them.
    Any other suggestions,
    I am very new to VB as i'm a data visualizer, so any help will be welcome
    Thanks

  4. #4
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: Automatically deleting values

    i just posted a sample code, not the perfect as u need, it just loop rows 1 to 100, u can find the last used row in column N like this
    Code:
    Dim LastRow as Integer
    LastRow = Cells(Rows.Count, "N").End(xlUp).Row
    then put the lastrow in loop
    Code:
    For i = 1 To LastRow
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  5. #5

    Thread Starter
    New Member
    Join Date
    May 2012
    Posts
    4

    Re: Automatically deleting values

    Still really struggling with this code like a nube.....
    Thanks for your help so far


    So let me try and re explain what I want to achieve,
    I want to delete entire Columns A B D G J K L M P Q

    and then delete entire rows when columns N and O both present the value 0.

    And so far this is what I have and its not working out for me.

    Dim i As Long, DelRng As Range
    For i = 1 To LastRow
    If Cells(i, "N").Value = 0 And Cells(i, "O").Value = 0 Then
    Set DelRng = Union(Cells(i, "A"), Cells(i, "B"), Cells(i, "D"), Cells(i, "G"), Cells(i, "J"), Cells(i, "K"), Cells(i, "L"), Cells(i, "M"), Cells(i, "P"), Cells(i, "Q"))'
    DelRng.ClearContents
    Dim LastRow as Integer
    LastRow = Cells(Rows.Count, "N").End(xlUp).Row

    End If
    Next

    what am I doing wrong?

    thanks in advance

  6. #6
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: Automatically deleting values

    want to delete entire column or entire row?
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


  7. #7

    Thread Starter
    New Member
    Join Date
    May 2012
    Posts
    4

    Re: Automatically deleting values

    delete entire columns of the coulumns i've mentioned ive mentioned.

    then delete entire Rows...only when Columns N and O contain 0.

    Does that make sense?

    thanks

  8. #8
    Just a Member! seenu_1st's Avatar
    Join Date
    Aug 2007
    Location
    India
    Posts
    2,170

    Re: Automatically deleting values

    to delete entire column D, try this
    Code:
    Cells(1, "D").EntireColumn.ClearContents
    'or
    Range("D:D").ClearContents
    Seenu

    If this post is useful, pls don't forget to Rate this post.
    Pls mark thread as resolved once ur problem solved.
    ADO Tutorial Variable types SP6 for VB6, MsFlexGrid fast fill, Sorting Algorithms


Posting Permissions

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



Click Here to Expand Forum to Full Width