-
Jul 11th, 2012, 08:48 AM
#1
Thread Starter
Junior Member
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.
-
Jul 11th, 2012, 09:37 AM
#2
Re: Excel (Delete if certain criteria are met)
welcome to the forum,
r u trying to do this in VBA or VB6?
-
Jul 11th, 2012, 09:51 AM
#3
Thread Starter
Junior Member
Re: Excel (Delete if certain criteria are met)
Seenu,
I am running Excel 2007 so I am guessing VBA?
-
Jul 11th, 2012, 10:08 AM
#4
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.
-
Jul 11th, 2012, 10:31 AM
#5
Thread Starter
Junior Member
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
-
Jul 11th, 2012, 10:43 AM
#6
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?
-
Jul 11th, 2012, 10:46 AM
#7
Thread Starter
Junior Member
Re: Excel (Delete if certain criteria are met)
Yes sorry- cut the entire rows that meet this criteria and paste in another sheet
-
Jul 11th, 2012, 10:57 AM
#8
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")
-
Jul 11th, 2012, 11:10 AM
#9
Thread Starter
Junior Member
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
-
Jul 11th, 2012, 11:12 AM
#10
Re: Excel (Delete if certain criteria are met)
if ur problem solved, pls mark thread as resolved using thread tools above.
-
Jul 11th, 2012, 11:36 AM
#11
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|