Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying a macro on excel
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying a macro on excel
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 In article , Shadu wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying a macro on excel
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying a macro on excel
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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
passing arguments from an excel macro to a word macro | Excel Discussion (Misc queries) | |||
Macro - Open Word with Excel macro | Excel Discussion (Misc queries) | |||
after creating macro button, closed excel then restarted excel | Excel Discussion (Misc queries) |