Assign an incoherent range to a variable

Question

I'm trying to get an incoherent range assigned to a variable but I just can't seem to get it right. Can anyone please help me?

My code looks like this:

    Dim tstRange As Range
    Dim strRange As String
    Select Case strSite
        Case "Lerum/Aspedalen"
            strRange = "B" & iLastRow & ", H" & iLastRow & ":L" & iLastRow
            With Kurtans_favorit
                Set tstRange = .Range(strRange)
                Set tstRange = .Range("B" & iLastRow, "H" & iLastRow & ":L" & iLastRow)
            End With

Neither the first nor the second way to assign the range to tstRange works (well, actually the first one does, but it is only giving me the content of the first cell in the range, which is not my intention).

I intend to use this range to check if they contain any values. If they do the user will get a question whether to overwrite or cancel the operation he/she is doing.

Answer 1

Take a look at this example:

Option Explicit

Private Sub OlaJohnson()

    PrintAllRangeMembers RangeString:="A1,C1:E1"

End Sub

Private Sub PrintAllRangeMembers(RangeString As String)

    ' Purpose:
    ' Iterate through all members of a range and print them to the Immediate window

    Dim IncoRange As Range
    Set IncoRange = ThisWorkbook.Sheets("Sheet1").Range(RangeString)
    
    Dim Cell As Variant
    For Each Cell In IncoRange
        Debug.Print Cell
    Next Cell

End Sub

The Sub PrintAllRangeMembers takes a Range (coherent or not) as an argument, iterates through all members via a For Each loop, and prints the values to the Immediate window.

You can perform whatever checks you need to make in the For Each loop. Printing the values of the cells was just to demonstrate that the expected behaviour results.