Insert Blank Rows

[colmguckian]

Hi
I am looking for a macro to count the number of blank rows immediately before Cell A12.
If the number of blank rows is less than 10 then I would like to insert blank rows until there are 10 blank Rows Inserted.
Can anybody help please.

 

[Joe4]

Are you only looking at entries in column A to determine if a row is blank, or might other columns come into play also?
If so, what are your possible columns with entries?

 

[colmguckian]

I am only Looking at ColumnA to determine if a row is blank

 

[Joe4]

Maybe something like this?

Sub MyInsertRows()
 
    Dim myBlankCount As Integer
    Dim cell As Range
    
'   Count blank rows in range A1:A11
    For Each cell In Range("A1:A11")
        If Len(cell) = 0 Then myBlankCount = myBlankCount + 1
    Next cell
    
'   Insert blank rows starting at row 11
    If myBlankCount < 10 Then Rows("11:" & 11 + (9 - myBlankCount)).EntireRow.Insert
    
End Sub

 

[colmguckian]

Thanks Joe 4 your code works very well