How can I count the spaces between these letters

Question

I want to count the spaces vertically between the letter groups eo,oo,oe and ee.  In the picture below in the example is G:7 and G:3 would have 3 empty spaces so I want to put a 3 in the box L:4 under oe.  Another example is between D:10 an D:8 is 1 space so I would put a 1 in the box I:9 under ee.  I can use a Macro or formula and appreciate all your help!!!!!

Answer 1

I ran the macro , it removes a row and no error, nothing else happens.  This is how it looks after I hit the button.  what do you think I am doing wrong?  I'll play  around with it as soon as I get off work. 

Thanks

I am confused... the picture of the worksheet you posted in your last message has nothing to do with the one you posted in your first message. This last one has no spaces to count. Why did you post it???

Answer 2

I'm so sorry Rick, I'm at our. Cafe and on my iPad.  We got hit with a large crowd ( we have an Airshow going on) and I posted the wrong pic and check the wrong sheet. Please forgive me for not double checking and getting back sooner. I'll be home in a couple more hours and on my desktop. Again, forgive me

Answer 3

I'm so sorry Rick, I'm at our. Cafe and on my iPad.  We got hit with a large crowd ( we have an Airshow going on) and I posted the wrong pic and check the wrong sheet. Please forgive me for not double checking and getting back sooner. I'll be home in a couple more hours and on my desktop. Again, forgive me

No problem, you just had me confused, that is all. So, are you still having a problem with the code I posted? Enjoy the rest of the airshow... you can answer me later (I'm going out for a bit myself anyway).

Answer 4

Okay, I got your file and I see what the problem is. You apparently created the table in D3:G51 by copying some formula results from somewhere and then using "Paste Special/Values" to put those values into the above range. What happened is those formula that output the empty text string ("") actually put a text string of zero characters into your cells (it is kind of what happens when you copy/paste numbers that were text... you don't get real numbers, you get text values that look like numbers). Even though the cells are formatted as General, Excel still sees them as text values. So, in my code, when I tried to create ranges of blank cells, Excel did not see them as true blanks, rather, it saw them as text strings containing no characters. Anyway, below is code modified to handle this problem automatically. I will note, however, that cell F3 contains a #VALUE! error (as a text string) but you cannot see it because you have all the non-lettered cells formatted as white text on a white background... but if you select cell F3 and look in the Formula Bar, you will see the #VALUE! error text. Because that should never have ended up in your data, I am going to let you delete it manually rather than include code to delete it. So, once you do clear cell F3, run the macro below and I think everything will work as you want it to.

Sub CountBlanks()

  Dim X As Long, LastRow As Long, Blanks As Range, Ar As Range

  LastRow = 51

  Cells(LastRow + 1, Columns.Count).Value = "X"

  With Range("D3:G" & LastRow)

    .Value = .Value

    .Offset(, 5).ClearContents

    Set Blanks = .SpecialCells(xlBlanks)

    For X = 4 To 7

      For Each Ar In Intersect(Blanks, Columns(X)).Areas

        If Ar.Row > 3 Then Ar(1).Value = Ar.Rows.Count

      Next

    Next

    .Offset(, 5).Value = .Value

    .Offset(, 5).SpecialCells(xlConstants, xlTextValues).ClearContents

    .SpecialCells(xlConstants, xlNumbers).ClearContents

  End With

  Cells(LastRow + 1, Columns.Count).Clear

End Sub

One final note... your grid ends at Row 51, but you never changed the assignment to the LastRow variable (see bold code line above) that I told you to do.... you had left it at 21... I corrected it to 51 for you, but you need to remember to change it on your own if you ever change the size of your grid in the future.

Rick,

This works PERFECTLY!!!  Thank you!!  Question, I have 2 main sheets that i will be adding this to.  How do I change the output of the number counts?  On this sheet (E-O)the letter are in columns M:P and the output where I want to place he number count is in AI:AL

This sheet EO has the letters in columns Q:T and the output numbers are in AM:AP,  I looked at the Macro an I could not find which ones to change besides the D3:G. 

Is there a way t get rid of the #Value in the cell and make it blank?  I understand why its there but prefer not to see if "if possible"  The 1 in K:4 should not be there as there is no letter in that column for it to count against, so the K:3 should be blank.  Does that make sense?

Thanks again Rick!!!