Help with vba Columns  and rows

Posted on 2013-08-30
Medium Priority
Last Modified: 2013-09-02
Problem Statement:
Check column by column to see if Column(x) Row4 is Interior Color is Gray color RGB(220, 230, 241)
Check all rows in Column(x) for content
If there is content, copy the content to Column(x), Row 2

Non Duplicates...
Check to see if the currently read contents are the same as the previously read contents
If they are not the same (No Duplicates)
      Write the contents to Cell Column (x), Row 2

excel vba check column for value
excel vba check column for duplicates

Example using the screen shot in the document…
1)      Column “H” Row “4” contains the characters “CT_001” in a cell that has a gray background RGB(220,230,241)
2)      Check (read) the next row in Column “H”. It contains the characters “No”.
3)      Write the Characters “No” into a “read” variable.
4)      Write that variable to Column “H” Row “2”
5)      Read the next row. In this case “No Response Data Found”
6)      Check to see if the characters are equal to the “read” variable.
7)      If they are not write the characters to Column “H” Row “2”
8)      Move the characters “No Response Data Found” to the ‘Read” variable ( want to ensure duplicates do not get written.
9)      In this case Column “H” Row “2” will now contain the Characters “No” and “No Response Data Found”
Code so far…
Sub FindGray

Dim col As Range, rg As Range
    Application.ScreenUpdating = False
    Set rg = ActiveSheet.UsedRange

If rg.Column <> 1 Then Range(Cells(1, 1), Cells(1, rg.Column - 1)).EntireColumn.Hidden = True
Range(Cells(1, rg.Column + rg.Columns.Count), Cells(1, ActiveSheet.Columns.Count)).EntireColumn.Hidden = True

For Each col In ActiveSheet.UsedRange.Columns
    If col.EntireColumn.Cells(4, 1).Interior.Color <> RGB(220, 230, 241) Then _ 

End Sub

Open in new window

Any help I can get with this would be greatly appreciated.
Please let me if you need more details or information.
Thanks in advance.

Please see the attached word file for problem re-statement and screenshot examples. Your help with this would be greatly appreciated.

Thanks in advance.
Question by:tesla764
LVL 31

Expert Comment

ID: 39454253
Upload a sheet with this issue. Show the results expected in the sheet.

Author Comment

ID: 39454261
Please see attached file "EE-Aggregate.docx'.
LVL 52

Expert Comment

by:Martin Liss
ID: 39454420
I'm not sure I can help but if I were to try I'd need your workbook.
LVL 14

Accepted Solution

Faustulus earned 2000 total points
ID: 39454562
Hello tesla764,
The code in the attached workbook does what your task describes - for better or for worse. Run the Sub Main to see the result. In order to adapt this code to your own workbook you may need to assign different values to the enums at the top of the code.
    Private Enum Nws                ' Worksheet parameters
        NwsSum = 2                  ' Row to show the summary in
        NwsTest = 4                 ' Test row
        NwsFirstDataRow             ' First row to collect data from
    End Enum
The FirstDataRow is the first row following NwsTest, i.e. row 5. If you need this to be another value just assign the value as you see it done for the other enumerations.

Author Closing Comment

ID: 39458648
Thank You. I have incorporated this into my program and with some adaptation this works very well. Thanks again for all your help.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
With the emergence of Office 365 as a superior email communication platform, many organizations have started switching over to it.  After migrating to Office 365, sometimes users, as well as organizations, will have to import PST files to Office 36…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question