Excel Type MisMatch with VBA to hide certain rows

Question

I realize there are already a number of great responses for dealing with VBA TypeMismatch errors in Excel, but they all seem quite case-specific and I'm admittedly too much of a VBA n00b to follow them.

So, I wondered if you might be able to help me with this specific predicament: I have made up a workbook and I want rows to automatically hide themselves if the value in column C is less than today's date. *I assigned the following Macro to an ActiveX Command Button:*

Sub Hide_PastOrders()
 Dim MyRange As Range, C As Range
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual
 Set MyRange = Range("d1:d1000")
 MyRange.EntireRow.Hidden = False
 For Each C In MyRange
     If IsDate(C.Value) And C.Value < Date Then
         C.EntireRow.Hidden = True
     End If
 Next
 Application.ScreenUpdating = True
 Application.Calculation = xlCalculationAutomatic
 End Sub

It was working fine until recently, but now sometimes results in a 'Type MisMatch' and I'm not sure why. It only seems to be an issue on a couple of the worksheets and they are all assigned the same macro.

I have also implemented an ActiveX command button to 'Show all rows':

Sub ShowAll_Click()
ActiveSheet.Cells.EntireRow.Hidden = False
End Sub

Haven't had any issues with this one.

Also, I'm not sure that this is relevant, but I have created a number of internal references in my document. i.e. typed "=A5" in B5 for example so that if there are changes to our rentals for multiple pieces of equipment, I would only have to type the information in once. This proves to be very frustrating because everytime I encounter a 'TypeMismatch' Error, it reverts certain cells to "=REF".

Please let me know if I'm taking the wrong approach or if you have any suggestions!

Thanks, Alexandra.

If Tarik's answer worked like a charm, please accept it.

Jon Crowell
– Jon Crowell

2013-08-10 05:43:36 +00:00
Commented Aug 10, 2013 at 5:43 — Jon Crowell
– Jon Crowell, Commented Aug 10, 2013 at 5:43

Tarik · Accepted Answer · 2013-08-10 15:19:29Z

3

The expression in the if statement is not short-circuited in VBA. Try this:

If IsDate(C.Value) Then
     If  C.Value < Date Then
         C.EntireRow.Hidden = True
     End If
End If

See http://en.m.wikipedia.org/wiki/Short-circuit_evaluation

edited Aug 10, 2013 at 15:19

answered Aug 9, 2013 at 20:10

Tarik

11.2k2 gold badges31 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2668306 Over a year ago

Worked like a charm! Thanks for the speedy response!

Reafidy Over a year ago

I really don't understand this site sometimes, more so the people on it. You try and help someone and people criticise you for it, without even saying why. Anyway I up-voted man. Your answer is fine in my opinion.

Tarik Over a year ago

@Reafidy Not that I have put a lot of effort on this one but look at this stackoverflow.com/questions/18046003/… and zero. Amazingly, I got 50 points for a little nothing: stackoverflow.com/questions/18092056/… I wonder what keeps me going...

Ioannis Over a year ago

+1. Keep going Tarik, there will always be those who appreciate and those who do not!

Reafidy Over a year ago

Yeah I understand your frustration, take a look at www.ozgrid.com/forum. I'm an admin there. Some really good people there, you would be welcome.

Collectives™ on Stack Overflow

Excel Type MisMatch with VBA to hide certain rows

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related