Highlight Entire Row based on single cell value

[upcea]

HELP! I have a huge spreadsheet that i need to select a bunch of rows. the rows i need to highlight are contingent on data in a single cell in column AA. How do I do this?

Once I highlight the rows, I would like to copy and paste in another sheet and maintain the blank rows that were not highlighted...make sense?

THANKS! and happy monday :/

 

[upcea]

what is happening is the rows I want to be copied to the new sheet are being cut from the origional sheet. the new sheet is a copy and paste of the whole doc- not just the rows i want.

 

[upcea]

what is happening is the rows I want to be copied to the new sheet are being cut from the origional sheet. the new sheet is a copy and paste of the whole doc- not just the rows i want.

 

[VoG]

That doesn't happen for me. What should be in column AA to cause the row to be copied?

 

[upcea]

Just an "I"

 

[VoG]

For me this only copies the rows where column AA value is I

Sub CopyRows()
Dim c As Range, r As Range
For Each c In Columns("AA").SpecialCells(xlCellTypeConstants)
    If c.Value = "I" Then
        If r Is Nothing Then
            Set r = c
        Else
            Set r = Union(r, c)
        End If
    End If
    c.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & c.Row)
Next c
If Not r Is Nothing Then r.EntireRow.Delete
End Sub

 

[upcea]

blahhh still not working! must be something on my end. thanks for all of your help though. im going to fiddle with it and hopefully figure this out. thanks again!

 

[upcea]

just thought of something- what if I wanted to just SELECT certain rows with cells in column "AA" containing "I" and then clear contents in the selected rows, could i do that?

 

[VoG]

Try

Sub ClearRows()
Dim c As Range
For Each c In Columns("AA").SpecialCells(xlCellTypeConstants, xlTextValues)
    If c.Value = "I" Then c.EntireRow.ClearContents
Next c
End Sub

 

[upcea]

AMAZING! thank you SO SO much!