VBA Code Needed

[prashanthdivya]

Hi All,</SPAN>


I have a dump which contains huge data and under source field I need to retain source A, Source B, and Source C and the rest of the data I need to delete. Please help me with a VBA code for this.</SPAN>

 

[Michael M]

Whoa...that's a pretty general question !!
Can you give us more ( a lot more) information. ??

 

[prashanthdivya]

Hi ,</SPAN>

I receive a huge dump on a daily basis. There is a column named Source in this dump and I have been told to retain only the data from three different sources i.e. Source A, Source B and Source C . There are data from other sources as well which I need to delete. But I believe using Auto filter I can have only two criteria’s. So I am need of VBA Code which would do this for me automatically</SPAN>

Ex:</SPAN>

Product</SPAN></SPAN>	Type</SPAN></SPAN>	Position</SPAN></SPAN>	Status</SPAN></SPAN>	Source</SPAN></SPAN>
A</SPAN></SPAN>	Bill </SPAN></SPAN>	Duplicate</SPAN></SPAN>	Taken</SPAN></SPAN>	SOURCE A</SPAN></SPAN>
B</SPAN></SPAN>	CS</SPAN></SPAN>	Delayed </SPAN></SPAN>	Action</SPAN></SPAN>	SOURCE D</SPAN></SPAN>
V</SPAN></SPAN>	HT</SPAN></SPAN>	fsda</SPAN></SPAN>	Request</SPAN></SPAN>	SOURCE B</SPAN></SPAN>
A</SPAN></SPAN>	HT</SPAN></SPAN>	gps</SPAN></SPAN>	Granted</SPAN></SPAN>	SOURCE R</SPAN></SPAN>
C</SPAN></SPAN>	NET</SPAN></SPAN>	andr</SPAN></SPAN>	QUERY</SPAN></SPAN>	SOURCE A</SPAN></SPAN>
F</SPAN></SPAN>	CCNA</SPAN></SPAN>	hwcse</SPAN></SPAN>	GFDDD</SPAN></SPAN>	SOURCE D</SPAN></SPAN>

<TBODY>
</TBODY>

 

[prashanthdivya]

Hi Michael,</SPAN>

I receive a huge dump on a daily basis. There is a column named Source in this dump and I have been told to retain only the data from three different sources i.e. Source A, Source B and Source C . There are data from other sources as well which I need to delete. But I believe using Auto filter I can have only two criteria’s. So I am need of VBA Code which would do this for me automatically</SPAN>

Ex:</SPAN>

Product</SPAN></SPAN>	Type</SPAN></SPAN>	Position</SPAN></SPAN>	Status</SPAN></SPAN>	Source</SPAN></SPAN>
A</SPAN></SPAN>	Bill </SPAN></SPAN>	Duplicate</SPAN></SPAN>	Taken</SPAN></SPAN>	SOURCE A</SPAN></SPAN>
B</SPAN></SPAN>	CS</SPAN></SPAN>	Delayed </SPAN></SPAN>	Action</SPAN></SPAN>	SOURCE D</SPAN></SPAN>
V</SPAN></SPAN>	HT</SPAN></SPAN>	fsda</SPAN></SPAN>	Request</SPAN></SPAN>	SOURCE B</SPAN></SPAN>
A</SPAN></SPAN>	HT</SPAN></SPAN>	gps</SPAN></SPAN>	Granted</SPAN></SPAN>	SOURCE R</SPAN></SPAN>
C</SPAN></SPAN>	NET</SPAN></SPAN>	andr</SPAN></SPAN>	QUERY</SPAN></SPAN>	SOURCE A</SPAN></SPAN>
F</SPAN></SPAN>	CCNA</SPAN></SPAN>	hwcse</SPAN></SPAN>	GFDDD</SPAN></SPAN>	SOURCE D</SPAN></SPAN>

<TBODY>
</TBODY>

 

[Michael M]

A couple of assumptions here
Columns are "A" to "E"
The text in column "E" is, actually "SOURCE ", otherwise you will have to change to suit

Sub tester2()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "E").End(xlUp).Row
For r = lr To 2 Step -1
    If Range("E" & r).Value <> "SOURCE A" And Range("E" & r).Value <> "SOURCE B" _
    And Range("E" & r).Value <> "SOURCE C" Then
    Rows(r).Delete
    End If
Next r
End Sub

 

[Mindpsyche]

A couple of assumptions here
Columns are "A" to "E"
The text in column "E" is, actually "SOURCE ", otherwise you will have to change to suit

Sub tester2()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "E").End(xlUp).Row
For r = lr To 2 Step -1
    If Range("E" & r).Value <> "SOURCE A" And Range("E" & r).Value <> "SOURCE B" _
    And Range("E" & r).Value <> "SOURCE C" Then
    Rows(r).Delete
    End If
Next r
End Sub

Hi Mike, I have a small question regarding the Step - 1 , is this to go back up one row once another row is deleted?

Example: If row 3 is deleted, then the activecell move up one row to repeat the loop? As once Row 3 is deleted, row 4 becomes row 3, and if we dont put the step then one row is omitted?

 

[sathish_fun]

hi

are you sure you want to delete the rows which dose not contains (Source A, Source B and Source C )

Give me some details

in which column these details will be available (Source A, Source B and Source C )


Column

A	B	C	D	E
Product	Type	Position	Status	Source
A	Bill	Duplicate	Taken	SOURCE A
B	CS	Delayed	Action	SOURCE D
V	HT	fsda	Request	SOURCE B
A	HT	gps	Granted	SOURCE R
C	NET	andr	QUERY	SOURCE A
F	CCNA	hwcse	GFDDD	SOURCE D

<tbody>
</tbody>

 

[prashanthdivya]

Hi Michael,

Thankyou for the reply but this code is taking a lot of time since the data that I have is Huge almost 3 lakhs. It would be helpful if it is faster.

 

[Michael M]

@Mindpsyche
Yeah, basically.
If you F8 through the code and then run your cursor over the "r" in the code you'll see that the "r" value decreases after each loop, which means it is working it's way back to line 2, one line at a time.
If you change the step-1 to step -2 it will only do every second line.

 

[Michael M]

Try

Sub tester2()
Dim lr As Long, r As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "E").End(xlUp).Row
For r = lr To 2 Step -1
    If Range("E" & r).Value <> "SOURCE A" And Range("E" & r).Value <> "SOURCE B" _
    And Range("E" & r).Value <> "SOURCE C" Then
    Rows(r).Delete
    End If
Next r
Application.ScreenUpdating = True
End Sub