Hide rows in excel if two cells match.

November 17, 2009 8:19 PM

Please help me create an excel macro to hide rows given a certain criteria.

I have an Excel 2003 spreadsheet with 2 columns of amounts (i.e. A and B) then a column with a date (C) and a bunch of other columns in between that I don't care about. Please help me create a macro to hide the row if columns A & B for that row are equal AND if C is less than or equal to a date I specify in a constant cell ($A$1).

Also, I need this to happen only if A, B or C are not null.

Salamat po.

posted by onich to Computers & Internet (9 answers total)

http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/5953cd060c814453
posted by dfriedman at 8:25 PM on November 17, 2009

Response by poster: Hmm... Thanks, dfriedman, but I forgot to mention that I have no experience with writing VB Script. Thanks, for pointing me to microsoft.public.excel.programming though. Lots of interesting stuff there.

A search pointed me to the script below:
Sub deleteblankrows()
Range("a1:a6").SpecialCells(xlCellTypeBlanks).EntireRow.hidden=true
End Sub

But, I'll need help tweaking this to fit what I need. Is there a way to plug in
Hide Row if A=B and C
Thanks.
posted by onich at 8:42 PM on November 17, 2009

Best answer:

Sub thing()

Range("A2").Select

While ActiveCell.Row <= ActiveSheet.UsedRange.Rows.Count

    If ActiveCell.Value = Selection.Offset(0, 1).Value _
     And Selection.Offset(0, 2).Value <> Cells(1,1).Value _
     And ActiveCell.Value & Selection.Offset(0, 1).Value & Selection.Offset(0, 1).Value <> "" Then
        Rows(ActiveCell.Row).Hidden = True
    End If
    Selection.Offset(1, 0).Select
    
    
Wend
end sub

posted by pompomtom at 8:43 PM on November 17, 2009

Response by poster: Ooh. More responses... Let me read and test these.
posted by onich at 8:43 PM on November 17, 2009

Response by poster: Thanks, pompomtom. Do you mind explaining what the code does functionally? Maybe just enough for me to tweak it specifically for my spreadsheet or if someone else intends to use this.

Sorry if I'm a little bit slow here. But I'll be able save hours if we can get this to work.
posted by onich at 8:55 PM on November 17, 2009

Functionally: nothing at all because AskMe keeps eating up important parts.

Memail me your email address and I'll send you a working version.
posted by pompomtom at 9:02 PM on November 17, 2009

Response by poster: Understood. Mailed you my email address. Thanks!!
posted by onich at 9:04 PM on November 17, 2009

Response by poster: It works! I'll post a link to text file after work.
posted by onich at 9:19 PM on November 17, 2009

Mod note: Did my best to repair pompomtom's comment, hope that looks right now.
posted by cortex (staff) at 9:22 PM on November 17, 2009