Slection of Rows on a Condition

[Guest]

I am analyzing performance of Projects, which are around 30000 rows of
information and 160 Columns of information. This is contained in a Worksheet
named Projects-06

I want to achieve the following in a blank worksheet in the same workbook,
by culling out information from the Worksheet Projects-06, selective 4
columns of information, depending on 2 conditions. (Cost<>0 and Status=â€Yâ€)

Col-A – Project-ID
Col-B- Project-Name
Col-C- Cost
Col-D- Status

I am a advanced foxpro programmer /excel user but a VB Novice.
I want to achieve this using a VB macro.

Any help will be greatly appreciated.

 

[Guest]

hi Ananth try this.

Sub Achieve()

Sheets("Projects-06").Activate
Dim r, rw, t
r = Range("A65500").End(xlUp).Row
rw = 1
For t = 2 To r
If Cells(t, 3) > 0 And Cells(t, 4) = "Y" Then
rw = rw + 1
Sheets("Blank").Cells(rw, 1) = Cells(t, 1)
Sheets("Blank").Cells(rw, 2) = Cells(t, 2)
Sheets("Blank").Cells(rw, 3) = Cells(t, 3)
Sheets("Blank").Cells(rw, 4) = Cells(t, 4)
End If
Next

End Sub

 

[Guest]

"excelent" skrev:

 

[Guest]

change
If Cells(t, 3) > 0 And Cells(t, 4) = "Y" Then
to
If Cells(t, 3) <> 0 And Cells(t, 4) = "Y" Then

 

[Guest]

Thanks very much; It worked.
Is it possible to retain the formating properties; For example, I have
colored a row in Green,. I would like to contine see that row in green in the
"Blank" worksheet also

 

[Guest]

try this one (changed a bit to speed up courses the color check)

Sub AchieveCol()

Sheets("Projects-06").Activate
Dim r, rw, t
r = Range("A65500").End(xlUp).Row
rw = 1
For t = 2 To r
If Cells(t, 3) <> 0 And Cells(t, 4) = "Y" Then
rw = rw + 1
Range("A" & t & "" & t).Copy Destination:=Sheets("Blank").Range("A" & rw &
"" & rw)
End If
Next

End Sub


"Ananth" skrev: