Excel – VB Excel ignore empty cells in range

excelvba

Is it possible to tell a range to ignore any empty cells. For example I start my macro with,

Dim v, stMember
v = Sheets("Home").Range("B12:B14")
For Each stMember In v

As there is a value in B12, B13 and B14 there shows no errors. But i would look to be able to expand the range to B22 for example, however if there is nothing in a cell in the range i get error messages. Its from a user input so they will never enter more than 10 values, but could possibly enter less.

Below is the full code but its quite long so my apologies if not neccessary.

Sub createSummary()


Dim Val As String

Val = Sheets("Home").Range("B3").Value

If SheetExists(Val) Then

    MsgBox "Summary for " + Val + " already exists."

Else

Sheets.Add.Name = Val
Sheets(Val).Select
ActiveCell.Offset(1, 0).Select

Dim v, stMember

v = Sheets("Home").Range("B12:B14")

For Each stMember In v

Dim ws As Worksheet
Dim lastrow As Long

Set ws = ThisWorkbook.Sheets(stMember)
lastrow = ws.Cells(Rows.Count, 2).End(xlUp).Row

    For i = 2 To lastrow
        ws.Activate
        If ws.Range("B" & i).Value = Val Then
            Range("B" & i).EntireRow.Select
            Selection.Copy
            Sheets(Val).Select
            ActiveCell.Offset(1, 0).Select
            ActiveCell.End(xlToLeft).Select
            ActiveCell.PasteSpecial paste:=xlPasteValues
            Range("J" & ActiveCell.Row).Value = stMember

        End If
    Next i
    Application.CutCopyMode = False

Next stMember
End If
End Sub

Best Solution

Simple IsEmpty() should do;

if Not IsEmpty(stMember) then
    ' do something when not empty
...
Related Question