1

i am trying to return a message if any cells in a given range are blank. If i declare the range i.e. 

Set OrderRng = [C1: C41]

it works fine, but if i declare it as such;

Set OrderRng = Range("c1:" & ActiveSheet.Range("c65536"). End(xlUp).Address).Select`

It does not work.

I know the second argument range is declared correctly as it always highlights the correct cells.

My entire code looks like this;

> Sub BlankCell() Dim OrderRng As Range On Error Resume Next
> 
>     Set OrderRng = Range("c1:" & ActiveSheet.Range("c65536").End(xlUp).Address).Select
> 
> ' Set OrderRng = [C1: C41]  ' Rm'd for testing
> 
>   Set OrderRng = OrderRng.SpecialCells(xlCellTypeBlanks)
> 
> If Err = 0 Then MsgBox "An Order ID is missing on one of your entries,
> please amend then try again" End If End Sub

What am i doing wrong, i know it will be obvious, but not to me.

Many thanks

Ben Rhys-Lewis
  • 3,118
  • 8
  • 34
  • 45
Prospidnick
  • 133
  • 1
  • 10
  • It would be helpful to share the error you are getting. In this case, probably "Type Mismatch" – JNevill Mar 23 '16 at 13:46
  • 1
    Please do not use `Activeheet` or `Usedrange` or even hardcode the cells to find the last row. You may want to see [THIS](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) Also avoid using `.Select`. You may want to see [THIS](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – Siddharth Rout Mar 23 '16 at 14:08

1 Answers1

3

To fix, just remove the .select from the end of your range variable set line:

Set OrderRng = Range("c1:" & ActiveSheet.Range("c65536").End(xlUp).Address)

The .select method returns a TRUE or FALSE value, so you end up with a type mismatch trying to Set OrderRng to TRUE, which is a boolean, not a range.

Also there's 100% no reason to select here and your code should continue on fine.

JNevill
  • 46,980
  • 4
  • 38
  • 63
  • Many thanks for your quick responses, i did not appreciate not using .select by doing do so i could see the range selected. – Prospidnick Mar 24 '16 at 13:51