Setting multiple cell reference

[tiredofit]

My workbook as 2 worksheets, codenames are Data and Input.

In Input, I have some cell references, such as:

A1
A2
A10

The code below looks at the cells in the worksheet Data.

    Dim CheckRng As Range
    
    With Input
    
        Set CheckRng = .Range(.Cells(1, 6), .Cells(10, 6))
    
    End With
    
    Dim CheckRngElement As Range
    
    For Each CheckRngElement In CheckRng
        
        If Data.Range(CheckRngElement).Value = vbNullString Then

            ' do something

        End If

    Next

This code works.

However, if one of the cell reference is multiple, it fails.

A1:A10
A11
B20:B30,B40:B50

How can I adapt my code so that even if the user enters a multiple cell reference, such as A1:A10, it would work?


Thanks

 

[steve the fish]

You need to do another loop eg:

Set sh = Sheets("Sheet1")
Set Data = Sheets("Sheet2")

Set CheckRng = sh.Range(sh.Cells(1, 6), sh.Cells(10, 6))

For Each CheckRngElement In CheckRng
    If Len(CheckRngElement.Value) > 0 Then
        Set rng = Data.Range(CheckRngElement.Value)
        For Each c In rng
            If Len(c) > 0 Then
                MsgBox c.Value
            End If
        Next
    End If
Next

 

[tiredofit]

You need to do another loop eg:

Set sh = Sheets("Sheet1")
Set Data = Sheets("Sheet2")

Set CheckRng = sh.Range(sh.Cells(1, 6), sh.Cells(10, 6))

For Each CheckRngElement In CheckRng
    If Len(CheckRngElement.Value) > 0 Then
        Set rng = Data.Range(CheckRngElement.Value)
        For Each c In rng
            If Len(c) > 0 Then
                MsgBox c.Value
            End If
        Next
    End If
Next

Thanks, that's exactly what I needed.