Check If range contains more than 6 spaces?

[123excel]

Hi, I am trying to do a tricky part and I really need help here.

I am trying to check how many blanks there are below the current cell, and if there is more than 6, then run some code. If there is not more than 6, I will check for 5, then 4 and so on until 0. I thought I could add this as an IF statement for 6 blanks then run some code, then a new IF statement for 5 blanks and run the same code and so on.. But if there is an even better solution containing less code that is of course even better.

There is also some other criterias that has to be fulfilled. These are that the first column of the row has to be the same as the value of "D35" and that range E45 has to be the value "Great". These work as they should, I only need help with the checking of blanks.

I have this code right now, but I dont know how to check if there is more than 6 blanks on the range below:
If .Cells(R, col) = Range("D35") And Range("E35").Value = "Great" And .Cells(R, col).Offset(0, 2).Resize(20, 0) >= 6 blanks in this range Then

See the full code below:

Sub Add_data()


    Dim col As Variant
    Dim BlankRows As Long
    Dim LastRow As Long
    Dim R As Long
    Dim StartRow As Long


        col = "Z"
        StartRow = 1
        BlankRows = 1


            LastRow = Cells(Rows.Count, col).End(xlUp).Row


            Application.ScreenUpdating = False


            With ActiveSheet
For R = LastRow To StartRow + 1 Step -1


If .Cells(R, col) = Range("D35") And Range("E35").Value = "Great"[B] And .Cells(R, col).Offset(0, 2).Resize(20, 0) = ""[/B] Then
.Cells(R, col).Offset(0, 0).Value = Range("D35").Value
.Cells(R, col).Offset(0, 2).Value = Range("A35").Value
.Cells(R, col).Offset(0, 3).Value = Range("B35").Value
Range("BJ33").Copy .Cells(R, col).Offset(0, 1)
End If

Next R
End With
 Application.ScreenUpdating = True

End Sub

 

[123excel]

Re: How to check If range contains more than 6 spaces?

Just to clearify and make it a bit more tricky..

The spaces are counted two columns at right offset (0, 2), but they also need to have the "D35" value in its own row in the column two steps at left (offset 0, 0).

So the spaces should only be counted if they have the D35 value in two column at left.

 

[NoSparks]

Re: How to check If range contains more than 6 spaces?

try this

... And Application.WorksheetFunction.CountIf(.Cells(R, col).Offset(0, 2).Resize(20), "") >= 6 Then

 

[123excel]

Re: How to check If range contains more than 6 spaces?

Yes, almost!

It counts the number of spaces in the range, but it needs to only count the spaces if the row of the blank has the D35 value two columns at the left (offset 0,0).

Any idea here?

 

[NoSparks]

Re: How to check If range contains more than 6 spaces?

(offset 0,0) = no offset

Your original code loops up column Z looking for cells that = D35
when it finds one, if cell E35= "Great",
you want to look at the range of cells in column AB, from the row where column Z = D35 and the 19 rows below,
and count how many blank cells are in that range.

if that's not what you're wanting can you re-phrase your original request to indicate what you're trying to accomplish ?

 

[123excel]

Re: How to check If range contains more than 6 spaces?

Almost. It loops Z to find cells that = D35. When it finds one, and cell E35 = "Great", then it should check how many cells that are empty 19 rows below (in column AB), but it should only count the empty cells in column AB if they have the D35 value in their Z column. If there is 6 or more empty cells with D35 value in Z and the other criterias are met, I run some other code.

Like this:
Z--------------AA-------------AB
D35 value-----Empty-----------Empty <- this should count as 2 empty rows (because its 2 empty rows below which has D35 value in Z).
Empty---------Empty----------Empty
D35 value-----Empty----------Empty <- 1
Empty---------Empty----------Empty
D35 value-----Empty----------Empty <- 2


Does this make it clearer?

 

[NoSparks]

Re: How to check If range contains more than 6 spaces?

Try this

Sub Add_data()
    
    Dim LastRow As Long, blankRows As Long
    Dim R As Long, StartRow As Long
    Dim rng As Range, cel As Range
    Dim col As Variant
        
    col = "Z"
    StartRow = 1

Application.ScreenUpdating = False
With ActiveSheet
    LastRow = .Cells(Rows.Count, col).End(xlUp).Row
    For R = LastRow To StartRow + 1 Step -1
        If .Cells(R, col) = Range("D35") And Range("E35").Value = "Great" Then
            Set rng = .Cells(R, col).Offset(1, 2).Resize(19)
            For Each cel In rng
                If cel.Offset(, -2).Value = .Range("D35") Then blankRows = blankRows + 1
            Next cel
            
            MsgBox "There are  " & blankRows & "  empty rows"
            ' You could use 'Select Case' here to run specific code based on blankRows.
        
        End If
        blankRows = 0
    Next R
End With
Application.ScreenUpdating = True
End Sub

 

[123excel]

Re: How to check If range contains more than 6 spaces?

Thank you so much!! I had to make a minor adjustment but now it seems to do what I was looking for!

Below is the full working code, I marked the adjustment I made in black and also added the case as you proposed (great idea btw, makes it a lot cleaner) and it all seem to work now! Will try it with my other code in the end of the weekend. Thank you so much for helping me out, I really appreciate you taking the time.

Sub Add_data()
    
    Dim LastRow As Long, blankRows As Long
    Dim R As Long, StartRow As Long
    Dim rng As Range, cel As Range
    Dim col As Variant
    Dim NumRow As String
   NumRow = blankRows
        
    col = "Z"
    StartRow = 1


Application.ScreenUpdating = False
With ActiveSheet
    LastRow = .Cells(Rows.Count, col).End(xlUp).Row
    For R = LastRow To StartRow + 1 Step -1
        If .Cells(R, col) = Range("D35") And Range("E35").Value = "Great" Then
            Set rng = .Cells(R, col).Offset(1, 2).Resize(19)
            For Each cel In rng
                If cel.Offset(, -2).Value = .Range("D35") [B]And cel.Value = ""[/B] Then blankRows = blankRows + 1
            Next cel
            Select Case NumRow
            Case Is < 3
            MsgBox "Less than 3"
            Case Is > 3
        MsgBox "More than 3"
            End Select
        End If
        blankRows = 0
    Next R
End With
Application.ScreenUpdating = True
End Sub

 

[NoSparks]

Re: How to check If range contains more than 6 spaces?

Glad to have helped.... but

That's not going to work as you are setting NumRow = blankRows at a time when blankRows = 0 and it will never change, you will always get the "Less than 3" message box.

Don't know why you would need the additional variable.
If for some reason the Long data type blankRows needs to be a string, which I kinda doubt because you are comparing it with the numeric 3, you can coerce it at time of need with Cstr(blankRows)

Be aware that Excel compares data types before even looking at contents of cells, what you have may work because both values are 'in memory', not on the sheet.
If you were to try to compare the string NumRow with a cell containing the integer 3, Excel would see is a string being compared to an integer and reject it before looking at the content of the cell.

Anyway, thanks for the feedback and good luck with your project.