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

See if this macro does what you want...

Sub CountBlanks()

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

  LastRow = 21

  With Range("D3:G" & LastRow)

    .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

End Sub

Note: You must set the LastRow variable equal to the row number for your last bordered cell so the macro knows how far down to count to.

Answer 2

An Excel formula solution:

Enter this formula in cell I3 & copy to all cells (columns & rows) in range:

=IFERROR(IF(AND(D3="",D2=I$1),MATCH(I$1,D4:D$200,0),""),"")

Please change the last row number in the range per your data range - presently we have mentioned 200 rows in formula: D4:D$200

Note - in your image, column K seems to be in error - numbers should probably be in cells K6, K10, ... instead of K5, K9, ...

____________________________________________

Rgds,

Amit Tandon

http://www.globaliconnect.com/

Excel & VBA

If this response answers your question then please mark as Answer.

Answer 3

See if this macro does what you want...

Sub CountBlanks()

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

  LastRow = 21

  With Range("D3:G" & LastRow)

    .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

End Sub

Note: You must set the LastRow variable equal to the row number for your last bordered cell so the macro knows how far down to count to.

Hi Rick,

I am getting all 1's in the cells

Answer 4

I tested the code before I posted it and I just tested it again... it does not report all 1's for me. Now the code does need a minor tweak, but that tweak will not address the problem you are reporting. Can you send me a copy of the workbook where my code does not work so I can trace the code first-hand?

Please mention the title for this thread in your email to me so I can more easily find this thread again when I have (hopefully) figured out why you are not getting the same results as I get.

For the record, this is the code with the minor tweak applied...

Sub CountBlanks()

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

   LastRow = 21

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

   With Range("D3:G" & LastRow)

     .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

Answer 5

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.