Differentiate Between "Blank" Cells and Data

**S-Hart** · 04-17-2015, 03:45 PM

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

**6StringJazzer** · 04-17-2015, 05:00 PM

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.

**S-Hart** · 04-17-2015, 05:18 PM

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.

**RedSummer** · 04-17-2015, 09:52 PM

Maybe something using these?

If _<> 0 Then _

If Not _ = vbNullString Then

**6StringJazzer** · 04-18-2015, 09:48 AM

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"))

**S-Hart** · 04-18-2015, 12:31 PM

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

**Norie** · 04-18-2015, 10:15 AM

You could use TRIM.

**6StringJazzer** · 04-18-2015, 03:51 PM

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:

Public Sub test()

   Dim R As Long
   R = 2 ' first row of data
    
   Do Until Cells(R, "G") = ""
      Debug.Print "Row " & R & " Column E is blank: " & (Trim(Cells(R, "E")) = "")
      R = R + 1
   Loop

End Sub

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

**Leith Ross** · 04-18-2015, 04:04 PM

Hello S-Hart,

It seems to me you want to format all the time cells on the report. You have 2 distinct formats "hh:mm:ss: for "In", "Out", and "Stop" while "Dwell" is "hh:mm".

This is easily accomplished by leaving the spaces in the cells. The code below will correctly format only cells with time values. This macro has been added to the attached workbook.

Sub FormatTimes()

    Dim Cell    As Range
    Dim Rng     As Range
    Dim Wks     As Worksheet
    Dim Word    As String
    
        Set Wks = Worksheets("Sheet1")
        
        Set Rng = Wks.Range("A1").CurrentRegion
        Set Rng = Intersect(Rng, Rng.Offset(1, 6))
        
            For Each Cell In Rng
                Word = Left(Cell, InStr(1, Cell, " "))
                Select Case Word
                    Case Is = "In ", "Out ", "Stop "
                        Cell.NumberFormat = "hh:mm:ss"
                    Case Is = "Dwell "
                        Cell.NumberFormat = "hh:mm"
                End Select
            Next Cell
            
End Sub

**S-Hart** · 04-18-2015, 09:15 PM

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.

**Leith Ross** · 04-18-2015, 10:20 PM

Hello S-Hart,

I am not sure why you are experiencing a delay in the cell's converting but I suspect you have some code you have not shown or told us about. I would check that Excel is set to "Automatic" calculation and not "Manual".

The number format should not change from "hh:mm:ss" to "h:mm:ss" on its own. The code worked perfectly on my computer using the workbook you had attached. I checked the formats were changed to the correct types.

**S-Hart** · 04-19-2015, 01:57 PM

Originally Posted by Leith Ross

I am not sure why you are experiencing a delay in the cell's converting but I suspect you have some code you have not shown or told us about. I would check that Excel is set to "Automatic" calculation and not "Manual".

Can confirm that Automatic Calculation is the option selected in the Formula subsection of the Excel Options.

The number format should not change from "hh:mm:ss" to "h:mm:ss" on its own. The code worked perfectly on my computer using the workbook you had attached. I checked the formats were changed to the correct types.

If I open the file you attached and immediately run the Macro, everything "looks" the same. If I right click on one of the times, it is still formatted as "General". If I double click it, the cell changes to "h:mm:ss". The code clearly says "hh:mm:ss" but it is actually changing it to "h:mm:ss". Likewise the "Dwell" time (although it doesn't really matter because it doesn't need formatting) formats to "h:mm" rather than "hh:mm" after I double click it.

This is the sort of thing that is confusing me. I don't understand why I am having to select each cell individually and perform an action in order for it to actually make the change, and then when it does make the change, it's not even the right change!

**Leith Ross** · 04-19-2015, 02:07 PM

Hello S-Hart,

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

**S-Hart** · 04-19-2015, 02:36 PM

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.

**Leith Ross** · 04-19-2015, 02:49 PM

Hello S-Hart,

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?

**S-Hart** · 04-19-2015, 02:56 PM

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.

**Leith Ross** · 04-19-2015, 03:14 PM

Hello S-Hart,

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

Change macro security settings in Excel

**S-Hart** · 04-19-2015, 03:20 PM

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!

**Leith Ross** · 04-19-2015, 04:17 PM

Hello S-Hart,

How is the report imported to Excel?

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

**S-Hart** · 04-19-2015, 05:49 PM

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.

**Leith Ross** · 04-19-2015, 06:34 PM

Hello S-Hart,

This is very strange. I have never encountered or heard of this happening till now. Your workbook ran for me on Windows 7 with Excel 2010 with no problems. Logically. it should do the same on your system at home if all system security and permissions are set the same, which have been verified. Honestly, I have no idea what is causing this. Perhaps someone else knows the answer.

EDIT: I have contacted someone I believe can help with this problem.

Differentiate Between "Blank" Cells and Data

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Re: Differentiate Between "Blank" Cells and Data

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Keeping particular cell blank if the related cells show "0" or "blank"

[SOLVED] Replace all BLANK cells in column with header title "Balance" to "0"

[SOLVED] How to USE """"" cells count """"" change font color

[SOLVED] Data validation: allow entry into a cell if other three cells have "X", "Y" and "Z"?

Excel 2007 : Match Values and Return "Present" "Absent" "Per Diem" or remain blank

Bookmarks

Bookmarks

Posting Permissions