Differentiate Between "Blank" Cells and Data

Greetings,

I have a big report that contains a column that I want to link to in a macro. I want it to perform one function if the cell contains ANY text, and do nothing if it is blank.

The problem I have is the way the report is generated I can't seem to find any way to make them differentiate. The cells that appear blank actually have a single space in them. They are all formatted as "General" - although I could change that if need be.

I have tried "replacing all" and removing the spaces, but that doesn't seem to work.

For now I have the processes that follow down, and they are working, the problem is that it either does it for all or none, and no matter what I try I can't get it to differentiate between the cells that have data, and the "blank" cells that have a space.

Does anyone have a suggestion? I've spent the best part of a day trying different combinations of things and can't seem to nail it.

-S

Originally Posted by 6StringJazzer

In VBA if you compare a cell value to "" it will be true only if the cell is empty. Also you can use ISEMPTY and it will be true only if the cell is empty.

Yeah I've tried variations of both of those but the issue definitely seems to be the way the report is generated, which unfortunately is beyond my control.

I either have "Location X" in the cell, or what appears to be a blank cell, but actually has a single space in it. I've also tried replacing every single space in the document with nothing, but despite removing all the spaces still doesn't work.

No matter what I seem to do it seems to either see both or neither, and never one or the other separately.

Maybe something using these?

If _<> 0 Then _

If Not _ = vbNullString Then

Can you attach a sample file? If it has private data, you can delete everything except the data you are trying to check for blanks. It would also be helpful to show what code you've already tried, because there could be some other error in your code.

I cannot imagine any situation where a cell with a blank space in it will cause a TRUE result for either

Range("A1") = ""

or

IsEmpty (Range("A1"))

Originally Posted by 6StringJazzer

Can you attach a sample file?

Sure.

Example.xlsm

This is just a cut down version of what I've been working with. Essentially what I am trying to do is select column F and columns H:AM, but only on the rows that contain the locations in column E. So if a location is listed, I want it to select. If there is no location in column E, then it doesn't need to do anything.

Funny thing is, I have conditional formatting set similarly to format in the same way (only the data in rows with a location in column E) and it is working just fine.

I couldn't even begin to tell you what I've tried it's been that many combinations.

My main goal at the moment is I want to select all those times on the rows with a location in column E so I can change it from the "General" to "hh:mm:ss." But specifically only that data.

Turning into a bit of a mess for me, and I'm sure there's an easy way.

-S

Now that I see your file (even though there is no code in it) I see I misunderstood what you are trying to do. You want to do something if a row is non-blank in column E, but skip if column E is empty or contains only spaces.

Norie has the right solution here. You don't have any code for me to modify so you can look at mine and adapt it. When I run this code on your data:

Please Login or Register  to view this content.

I get this result:

Row 2 Column E is blank: False
Row 3 Column E is blank: False
Row 4 Column E is blank: True
Row 5 Column E is blank: True
Row 6 Column E is blank: True
Row 7 Column E is blank: False
Row 8 Column E is blank: True
Row 9 Column E is blank: True
Row 10 Column E is blank: True
Row 11 Column E is blank: False
Row 12 Column E is blank: True
Row 13 Column E is blank: True
Row 14 Column E is blank: True
Row 15 Column E is blank: False
Row 16 Column E is blank: True
Row 17 Column E is blank: True

Thanks for the suggestions guys, that does indeed seem to be working. I am using the one from the example attached above.

The issue I am having now is that despite it being specified in the code as "hh:mm:ss" when I check the cell format, it's actually "h:mm:ss" instead. It's not the end of the world, but I would really rather it display 05 instead of 5.

And from there, the next issue is that whilst it has changed the cell format, it doesn't actually evaluate the cell and appear as changed until you select the cell and press enter. What's the quickest way to batch do this to the same cells? Link them to an empty sell and Paste Special -> Add?

Appreciate the help, definitely making progress for a newb.

Originally Posted by Leith Ross

What you are describing is not typical behaviour. Do you experience this same problem with other workbooks?

Yeah, I guess so. It's the first time I've ever noticed it though. But it's the same with all the workbooks I'm currently using. I searched around and saw other people were selecting all the relevant cells and using Paste Special and either add to a blank cell, or multiple by a cell with 1 in it. So I figured it was at least somewhat normal being that other people were having to do that.

I am actually on a relatives computer whilst on vacation, so for the sake of eliminating that as the cause, I logged into my work computer via remote desktop, downloaded the example above and tried it on that. Exact same result. I have to actually double click the cell, and then press enter for it to follow through and change the cell.

Originally Posted by Leith Ross

It seems to me that the macro is not executing nor causing an error when you call it. Have you checked your "Trust Center" settings are allowing VBA macros to run?

You could be right. I thought that it was running but just forcing me to click the cells. But now looking at the original report, with no Macros and nothing run, it performs the same. If I click in the cell and press enter, it does automatically change it to "h:mm:ss".

So it would seem perhaps it is not actually running.

What's the specific option that I should check? I checked and everything looks to be in order, but perhaps I am missing something.

Originally Posted by Leith Ross

The link below should answer your questions. It will walk you through what to do.

Yep, I already had all of that taken care of. Enable All Macros has always been selected, and the "Trust access to the VBA project object model" box is checked, not that I'm sure if that makes a difference.

Utterly confused now!

Originally Posted by Leith Ross

How is the report imported to Excel?

The report comes from a third party, and when it is generated I have the option (I think) of CSV, Excel 2003, or Excel 2007+. I get it in the latter, as I am using Excel 2007 here, and 2010 at work.

Also, is there any code you have not shown me that you are using with this workbook?

No. When I am trying to do it with the example you provided, there is nothing else open. The only code/available macro is what you have provided.