0

First post here and i am very new to the world of Excel-VBA.

I am trying to automate some reports and have run into a little snag. i am trying to hide rows on an excel worksheet based on the value of cells in 2 seprate columns. i have given given an example below. (The real Document can contain up to 50 names at one given time and pretty much any date.)

For this example i would need to hide all rows that do not have the value JIM in column A and have a greater date than todays date in column B. (04/05/2014)

A       B
JIM     04/05/2014
JIM     04/05/2014
BOB     05/05/2014
BOB     04/05/2014
TED     07/05/2014
TED     04/05/2014
BOB     09/05/2014
JIM     04/05/2014
JIM     11/05/2014
TED     12/05/2014

I am using the below code at the minute but ran into some issues when adding a second argument.

Sub Delete_Name_Date()

Dim rng As Range, cell As Range

For Each cell In Range("A1:F15")
If cell.Value = "JIM" Then
cell.EntireRow.Hidden = False
Else: cell.EntireRow.Hidden = True

End If
Next


End Sub

Any help would be great.

Thanks

Adam

Kataki
  • 5
  • 2
  • 5

1 Answers1

1

Try this one:

Sub Delete_Name_Date()
    Dim cell As Range

    For Each cell In Range("A1:A15")
        With cell
            .EntireRow.Hidden = _
                (.Value <> "JIM" And CDate(.Offset(, 1).Value) > Date)
        End With
    Next
End Sub

also for reliability I suggest you to use ThisWorkbook.Sheets("SheetName").Range("A1:A15") instead Range("A1:A15")

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
  • Hey Simoco, Thanks for getting back to me so quickly. The code works fine on the example however i seem to be getting the following error when trying to use it in my document :( Run-ime Error '13' Type Mismatch any idea why this could be? the data in the document is the same as the example provided. Cheers Adam – Kataki May 04 '14 at 15:50
  • two reasons `1)` your range `Range("A1:B15")` contains errors like `#N/A` `#DIV/0` and etc.. `2)` your dates stored with wrong date format and `CDate(.Offset(, 1).Value)` causes the error. Try to change `CDate(.Offset(, 1).Value)` to `DateValue(.Offset(, 1).Text)` – Dmitry Pavliv May 04 '14 at 15:55
  • Hey Simoco came back to this today and you were right the range contained errors amended and everything is working fine. You’re a life saver. Thanks again – Kataki May 06 '14 at 20:35