Checking if a Row is empty - CountA not working

jimayers

Board Regular
Joined
Nov 14, 2010
Messages
98
Hello - I am having a lot of trouble trying to get this line to work:
Code:
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:
Code:
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
 

jimayers

Board Regular
Joined
Nov 14, 2010
Messages
98
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,498
Office Version
2010
Platform
Windows
Give this a try...
Code:
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
 
Last edited:

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,112
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

Board Regular
Joined
Nov 14, 2010
Messages
98
Give this a try...
Code:
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

Board Regular
Joined
Nov 14, 2010
Messages
98
Give this a try...
Code:
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

Well-known Member
Joined
Sep 19, 2005
Messages
5,112
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

Board Regular
Joined
Nov 14, 2010
Messages
98
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

Board Regular
Joined
Nov 14, 2010
Messages
98
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
 

Forum statistics

Threads
1,081,543
Messages
5,359,431
Members
400,526
Latest member
Brook1083

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top