• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting rows with no color from Table # 1 to create Table # 2

Sheela

New Member
In one worksheet, I have one Table: Table # 1
I have some rows that are colored coded.
I wish to create a second table (Table # 2) to the right of the first table where I wish to extract the rows that have no color.

Question:
How do I extract data from table # 1 on (Left) to table # 2 (Right) in the same worksheet skipping the colored rows.
Is there a macro or a VBA code that will do this?
Right now I am selecting the no color cells one by one.
This will help me automate my task immensely.

Thanks for your help.

Sheela
 

Attachments

  • Question-Feb05-2016.xlsx
    9.2 KB · Views: 4
Is there any guiding logic as to why some rows are colored and some aren't? Or, you can apply an AutoFilter and filter on no color.
upload_2016-2-5_13-26-50.png

Alternatively, try running this macro.
Code:
Sub CopyRows()
Dim lastRow As Long
Dim recRow As Long
Dim startRow As Long
Dim i As Long

'Where does our data begin?
startRow = 3

'Where will output begin?
recRow = 3

'Find last row with data
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

Application.ScreenUpdating = False
For i = startRow To lastRow
    'Check if cell has no color
    If Cells(i, "A").Interior.ColorIndex = xlNone Then
        'Copy the values over to col G
        Cells(i, "A").Resize(1, 5).Copy
        Cells(recRow, "G").PasteSpecial xlPasteValues
        recRow = recRow + 1
    End If
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
 
Back
Top