Another Range Calculation with cell references

[Spikenaylor]

Hi, I am having a problem getting this to work.

Dim CellsFilled As Integer, cref1 As String, cref2 As String, cref3 As String, cref4 As String, cref5 As String, rng As Range, rng1 As String, rng2 As String
cref1 = "C2"
cref2 = "C7"
cref3 = "D"
CellsFilled = GetCellsFilled(cref1, cref2) ' gets a count of how many cells are filled with data, in this example, it is 5
cref4 = cref1 & ":" & cref3 & CellsFilled
cref5 = "H2:J5"
Set rng = ActiveSheet.Range(cref4, cref5)  ' [COLOR=#FF0000]This does not work
[/COLOR]'Set rng = ActiveSheet.Range(cref4)  '[COLOR=#00FF00] This works[/COLOR]
'Set rng = ActiveSheet.Range(cref5)  '[COLOR=#00FF00] This works[/COLOR]
'Set rng = ActiveSheet.Range("C2:D5, H2:J5")    '[COLOR=#00FF00] This works[/COLOR]
Call checkcells(rng, CellsFilled)

Sub checkcells(rng As Range, CellsFilled As Integer)
Dim r As Range, a As Integer
Dim bCellsFilled As Boolean
bCellsFilled = True
    For Each r In rng
        If r.Value = "" Then
        bCellsFilled = False
        End If
    Next r
    If bCellsFilled = True Then
    MsgBox " All cells in the first " & CellsFilled - 1 & " rows of the batch contain data"
    Else: MsgBox " Some Cells in the first 3 rows of the batch are Empty, please recheck before cicking the button"
    End
    End If
    
End Sub

 

[VoG]

Try

Set Rng = ActiveSheet.Range(cref4 & "," & cref5)

 

[Spikenaylor]

Doh,

Why is it the simplist things confuse you.

Hadn't evan thought of that.
Many Thanks
Mike

 

[snb_]

simplest ?

Set rng = ActiveSheet.cells.specialcells(2)</pre>