Checking if a Row is empty - CountA not working

[jimayers]

Hello - I am having a lot of trouble trying to get this line to work:

If WorksheetFunction.CountA(bFind) = 0 Then Goto a:

I have 3 columns and the following code looks for a value "B2" (through "B5") in row(3) and then inserts a blank row on the first instance each is found. It works for the most part, except for the CountA function:

Sub Insert_Blank_Rows()
Dim Lastrow, bFind As Range
Dim i As Integer


For i = 2 To 5
    Lastrow = Cells(Rows.Count, 3).End(xlUp).Row
    Set bFind = ActiveSheet.Range("C5:" & "C" & Lastrow).Find(What:="B" & i, LookIn:=xlValues, LookAt:=xlPart)
    
    If bFind Is Nothing Then GoTo a:    
    [SIZE=2][B]If WorksheetFunction.CountA(bFind) = 0 Then Goto a:[/B][/SIZE]
       ''''If bFind = "" Then GoTo a: (this doesn't work either)
    bFind.EntireRow.Insert shift:=xlUp  ' inserts row
    Rows(bFind.Row - 1).RowHeight = 9  ' sets row height
a:
Next i
End Sub

I need the code to properly find the empty row so I can insert more code based on the empty row. By inserting message boxes I was able to determine that the code does not work.
Can anybody help?
Thanks for your help - Jim A

 

[sheetspread]

Counta will include ""s

 

[jimayers]

Counta will include ""s

Hi, thanks for your response.
When I open the worksheet and enter the funtction on the sheet "=counta(C1:C3)" it returns a zero if the row (or those 3 cells in the row) are blank.
I am not seeing what the difference is or what I am doing wrong.
Thanks again - JA

 

[Rick Rothstein]

Give this a try...

RowToCheck = 12
If Len(Join(Application.Index(Rows(RowToCheck).Value, 1, 0), "")) = 0 Then
  ' Row has no displayed values...
  ' Could have formulas displaying "" though
End If

 

[sheetspread]

I am not seeing what the difference is

Type ="" into A1, then in B1 type =isblank(A1), it will say false. Then delete A2 and A3, keeping A1 as ="". In B1, type = counta(a1:a3), it will say 1. That ="" looks blank, it's invisible, but Excel does not see it as a true blank.

 

[jimayers]

Give this a try...

RowToCheck = 12
If Len(Join(Application.Index(Rows(RowToCheck).Value, 1, 0), "")) = 0 Then
  ' Row has no displayed values...
  ' Could have formulas displaying "" though
End If

How I wrote the code using countA returns a number if cells have "". What is the difference here?
Thanks - Jim A

 

[jimayers]

Give this a try...

RowToCheck = 12
If Len(Join(Application.Index(Rows(RowToCheck).Value, 1, 0), "")) = 0 Then
  ' Row has no displayed values...
  ' Could have formulas displaying "" though
End If

Could I write something that clears the empty rows immediately after my code "inserts" it. My code is inserting the blank row after all, and that is why I cant get my head around those inserted blank rows having anything at all in them.
Thanks - JA

 

[sheetspread]

When looping in ascending order, the row #(s) changes as soon as you insert, which is why you have to adjust the variable or just loop backwards.

 

[jimayers]

Type ="" into A1, then in B1 type =isblank(A1), it will say false. Then delete A2 and A3, keeping A1 as ="". In B1, type = counta(a1:a3), it will say 1. That ="" looks blank, it's invisible, but Excel does not see it as a true blank.

I see that...What I am having trouble figuring out is why those cells have anything in them at all since it is my code that just "inserted" them.
Thanks for your help, at least I am not beating myself over the head trying to get it to work - JA

 

[jimayers]

When looping in ascending order, the row #(s) changes as soon as you insert, which is why you have to adjust the variable or just loop backwards.

OH...thanks! I see others looping backwards and I didn't know why...I'll try that. In my code above I thought I accommodated for that by redefining "Lastrow" in my loop...guess I was off somewhere.
JA