An 'IF NOT ISEMPTY' syntax query

[Kayote]

Hi,

I wrote a macro as practice and in that macro I used the 'UsedRange' to identify the columns and rows being used up by data.

then I used the following syntax to identify any empty columns in between so they can be ignore by the code and the code can move on to the next column.

Please Login or Register  to view this content.

What the above code is meant to do is ignore the column no. 'iCol' if its empty. However VBA does not like it and while it doesnt give me any sort of error, it does not bypass the empty column either. What am I doing wrong here?

Also, even though I have read a bit on Integer and Double (& singles), I still am not comfortable with the differences. TO my mind, there would be hardly any use for doubles to be specifed as data type, can anyone shed some light please.

Cheers,

[shg]

Please Login or Register  to view this content.

[mewingkitty]

I usually go with something along the lines of
if cells(x, y) <> "" then

or

if cells(x, y) <> vbNullString then

And your question is a good one regarding doubles, singles, integers, etc. I think what it ultimately comes down to is efficiency. The more simple you can keep a program, the faster it will run. This doesn't seem like a problem on a small scale, but if you're dealing with a massive amount of calculations, you'll want to declare everything in the simplest fashion possible to speed up your code.

[Kayote]

Please Login or Register  to view this content.

Shg, thankyou again for the help. Out of curiosity what is wrong with my code and should it not work?

Cheers again

[Kayote]

I usually go with something along the lines of
if cells(x, y) <> "" then

or

if cells(x, y) <> vbNullString then

And your question is a good one regarding doubles, singles, integers, etc. I think what it ultimately comes down to is efficiency. The more simple you can keep a program, the faster it will run. This doesn't seem like a problem on a small scale, but if you're dealing with a massive amount of calculations, you'll want to declare everything in the simplest fashion possible to speed up your code.

Cheers Mewingkitty for the info. So atleast for the time being, I can just ignore all the other numbers and just keep my focus on the integers for defining numbers

All the best,

[Leith Ross]

Hello Kayote,

If row 1 is header row, here is another method you can use...

Please Login or Register  to view this content.

[shg]

From Help:

IsEmpty(expression)

The required expression argument is a Variant containing a numeric or string expression. However, because IsEmpty is used to determine if individual variables are initialized, the expression argument is most often a single variable name.

Remarks

IsEmpty returns True if the variable is uninitialized, or is explicitly set to Empty; otherwise, it returns False. False is always returned if expression contains more than one variable. IsEmpty only returns meaningful information for variants.

What you're testing with IsEmpty(someRange.Value) is the variant that the Value property returns. If there is more than one cell in the range (e.g., your code and Leith's suggestion), it will always return False.

[Kayote]

Leith Ross and Shg, thankyou ever so much for the help.

Regards,