Excel VBA Validation List to Comma Separated List in Cell

Question

I'm trying to create VBA code to run through a worksheet (and eventually a workbook) and generate comma separated lists for each cell which has validation. I was able to get achieve my goal on a defined range using the the code below:

Sub ValidationPrintOut2()

    Dim cell As Range
    Dim oldstr As String
    Dim newstr As String

    Dim usedcell As Range

    For Each usedcell In ActiveSheet.Range("N1:N3")
        For Each cell In Range(usedcell.Validation.Formula1)
            oldstr = ActiveSheet.Cells(usedcell.Row, usedcell.Column + 2)
            newstr = cell.Value

            ActiveSheet.Cells(usedcell.Row, usedcell.Column + 2) = oldstr + ", " + newstr
        Next cell
    Next usedcell
End Sub

But when I tried to expand it the code to the used range in a column (below) the code ends up breaking with a Method Error '1004': Method 'Range' of Object '_Global' failed.

Sub ValidationPrintOut2()

    Dim cell As Range
    Dim oldstr As String
    Dim newstr As String

    Dim usedcell As Range

    For Each usedcell In ActiveSheet.UsedRange.Columns("N")
        For Each cell In Range(usedcell.Validation.Formula1)
            oldstr = ActiveSheet.Cells(usedcell.Row, usedcell.Column + 2)
            newstr = cell.Value

            ActiveSheet.Cells(usedcell.Row, usedcell.Column + 2) = oldstr + ", " + newstr
        Next cell
    Next usedcell
End Sub

Could someone explain why this is happening and how to fix the problem? Thanks!

Problem isn't the range but the fact that some cells don't have validation and there is no error handling to avoid stopping everything from running — Automate This

SJR SJR · Accepted Answer · 2017-10-06T18:42:09

You can use Intersect and SpecialCells to only cycle through cells with validation. The On Error line is to avoid an error message if there are no such cells (which is probably what caused yours).

Sub ValidationPrintOut2()

Dim cell As Range
Dim oldstr As String
Dim newstr As String

Dim usedcell As Range

On Error Resume Next
For Each usedcell In Intersect(ActiveSheet.UsedRange, Columns("N").SpecialCells(xlCellTypeAllValidation))
    For Each cell In Range(usedcell.Validation.Formula1)
        oldstr = ActiveSheet.Cells(usedcell.Row, usedcell.Column + 2)
        newstr = cell.Value
        ActiveSheet.Cells(usedcell.Row, usedcell.Column + 2) = oldstr + ", " + newstr
    Next cell
Next usedcell

End Sub

Excel VBA Validation List to Comma Separated List in Cell

1 Answers