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 |
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 |
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. |
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!!! |
All times are GMT +1. The time now is 12:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com