Results 1 to 11 of 11

Thread: Excel (Delete if certain criteria are met)

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    27

    Excel (Delete if certain criteria are met)

    Hi Guys,

    I am a newbie to VB and would like some help re a Sub I am trying to create if possible in excel. The aim is:

    -Check the values of column E

    -If any two or more rows have the same value of column E, Then:

    -Check the corresponding SUM of the values of column S (which will be two or more as determined above)

    -If the corresponding sum is 0, cut and paste the entire rows concerned (i,e, which have the same values of column E) into a new sheet

    Cheers for any help.
    Indy.

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

    Re: Excel (Delete if certain criteria are met)

    welcome to the forum,
    r u trying to do this in VBA or VB6?
    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
    Junior Member
    Join Date
    Jul 2012
    Posts
    27

    Re: Excel (Delete if certain criteria are met)

    Seenu,

    I am running Excel 2007 so I am guessing VBA?

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

    Re: Excel (Delete if certain criteria are met)

    hav u tried anything so far? if yes post the code and tell us where u stuck.
    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
    Junior Member
    Join Date
    Jul 2012
    Posts
    27

    Re: Excel (Delete if certain criteria are met)

    Unfortunately I have not got very far so far, this is what i have...many thanks for your help btw


    Dim ISIN1 As Variant
    Dim Dte As Variant
    Dim NetAmount As Variant
    Dim FirstRow As Long
    Dim LastRow As Long
    Dim LRow As Long

    With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False

    'With ActiveSheet (enter the correct sheet here)

    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView
    .DisplayPageBreaks = False


    FirstRow = 10
    LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

    For LRow = LastRow To FirstRow Step -1 'is this step enough, want to check entire sheet?

    'If Range("E",LRow).Value = same as any other column E value in the sheet

    'And Range("S",Lrow).Value + Value of any other column S identifed = 0

    'Then Cut entire row and paste in 'excluded due to netting tab'

    Next LRow

    With Application
    .ScreenUpdating = True
    .Cacluation = CalcMode
    .Calculation = xlCalculationAutomation

    End Sub

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

    Re: Excel (Delete if certain criteria are met)

    'Then Cut entire row and paste in 'excluded due to netting tab'
    do u mean to paste in another sheet?
    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
    Junior Member
    Join Date
    Jul 2012
    Posts
    27

    Re: Excel (Delete if certain criteria are met)

    Yes sorry- cut the entire rows that meet this criteria and paste in another sheet

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

    Re: Excel (Delete if certain criteria are met)

    this is an example of cut and paste to another sheet
    Code:
    Range("A6").EntireRow.Cut Destination:=Sheet2.Range("A1")
    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


  9. #9

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    27

    Re: Excel (Delete if certain criteria are met)

    Cheers.

    The thing I am struggling with is cutting the range of rows which meets the criteria

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

    Re: Excel (Delete if certain criteria are met)

    if ur problem solved, pls mark thread as resolved using thread tools above.
    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


  11. #11
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: Excel (Delete if certain criteria are met)

    Excel VBA thread moved to Office Development

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