Set Range to Unknown Range - vba Code

[Plokimu77]

Good Morning,

Can someone help me modify my code, to select all the rows available starting in B1 ?
The range of rows can vary so basically its unknown to me, until I pull the data from my source.

Thank you

Sub Test()
Dim rng As Range, rcell As Range
Set rng = Range("B1:B23")
For Each rcell In rng.SpecialCells(xlCellTypeBlanks)
If Application.CountIf(rcell.Resize(, 18), "<>") = 0 Then
rcell.Resize(, 13).Interior.ColorIndex = 15
End If
Next rcell

 

[MARK858]

Try...

Set rng = Range("B1:B" & Range("B" & Rows.count).End(xlUp).Row)

 

[Plokimu77]

It worked perfect.

Thank you

 

[Plokimu77]

One more request.

(sorry, it just came to my mind as I was analyzing my data)

In addition, could it be possible to change the height of the blank rows to RowHeight = 6, as well?

Thank you

Sub Test()

Dim rng As Range, rcell As Range
       Set rng = Range("B1:B" & Range("B" & Rows.count).End(xlUp).Row)

For Each rcell In rng.SpecialCells(xlCellTypeBlanks)
If Application.CountIf(rcell.Resize(, 18), "<>") = 0 Then
rcell.Resize(, 13).Interior.ColorIndex = 15
End If
Next rcell

End Sub

 

[MARK858]

Sub Test()

    Dim rng As Range, rcell As Range
    Set rng = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)

    For Each rcell In rng.SpecialCells(xlCellTypeBlanks)
        
        If Application.CountIf(rcell.Resize(, 18), "<>") = 0 Then
            rcell.Resize(, 13).Interior.ColorIndex = 15
            rcell.EntireRow.RowHeight = 6
        End If
    
    Next rcell

End Sub

 

[Plokimu77]

Cool.

Thank you!

 

[MARK858]

You're welcome