Trying a macro on excel

S

Shadu

I need to do a filter of sorts using the color of the rows as the basis for
this filter. i am working on a "For" loop that should go row by row and
checking to see if the row color is "no fill" if it is it should cut it and
paste it on another workbook i have already made. i can select the work book
and all but i don't know how o check the row color (was thinking of checking
the cell (A(i)) wher i is the number in the for loop and using a similar
method for the cutting and pasting of the rows. how can i do it. i'll post
what code i have so you see what i'm working with.

PS i am very rusty with my VB coding so please be understanding if the code
is mostly wrong... :)

Sub Test4()
Dim i as integer
Dim pos as integer
pos =1
For i=3 to 7215
Windows("DE.xls").Activate
Range("A"+i).Select //this is where i can't get it to work
if (Selection.Interior.Color = xlNone) then // don't know if this is
right
Rows(""+ i":"+i"").Select
Selection.Cut
Windows("test.xls").Activate
Rows(""+pos":"+pos"").Select //how should i do this
ActiveSheet.Paste
pos= pos+1
end If
next i
 
J

JE McGimpsey

One way:

It's more efficient to build a copy range, then copy all at once rather
than doing it row by row:

Public Sub Test5()
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim rCell As Range
Dim rCopy As Range
Dim rDest As Range

Set wsSource = Workbooks("DE.xls").Sheets(1)
Set wsDest = Workbooks("test.xls").Sheets(1)

With wsSource
For Each rCell In .Range(.Cells(3, 1), _
.Cells(.Rows.Count, 1).End(xlUp))
With rCell
If .Interior.ColorIndex = xlColorIndexNone Then
If rCopy Is Nothing Then
Set rCopy = .Cells
Else
Set rCopy = Union(rCopy, .Cells)
End If
End If
End With
Next rCell
End With
If Not rCopy Is Nothing Then
With wsDest
Set rDest = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
With rCopy.EntireRow
.Copy Destination:=rDest
.Delete
End With
End If
End Sub
 
S

Shadu

I'm going to try it and see... i welcome anything that makes it more
efficient as long as it dos what i need it to do.
 
S

Shadu

Worked like a charm THank you very much. Now if i need to i just have to
modify the color for any other filter. Thnx man!!!
 
Top