Excel dropdown to entire column

Question

How to copy a dropdown menu (data validation) to entire column in Excel (only rows that having something else too). And, in this case, how to leave row for the header?

Answer 1

1. Select the entire column you want to validate

2. Click on "Data Validation" from the Data tab

   

3. Choose "List" from the "Allow" box

   

4. Click on the arrow to the right of the "Source" box and select the cells containing the allowed values

   

And you are good to go!

More on that here.

Answer 2

To copy and paste only the dropdown list (validation), and only to not empty cells, do the following:

1. Select every possible cell (including empty ones) where you want to have the dropdown menu pasted.

2. Hit F5, then Special..., finally Constants radio button and OK. This will select only cells which are not empty from your previous selection.

3. Home > Paste > Paste Special...

4. Validation radio button and lastly OK.

Let me know if it helps!

Answer 3

Ok, I found the answer and now it's working:

Sub pasteCellToColumn()
    Dim lastRow As Long, i As Long

    lastRow = Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
    Sheets("hiddenData").Range("A1").Copy

    For i = 1 To lastRow
        If Len(Trim(Sheets("Sheet1").Range("A" & i).Value)) <> 0 Then
        Sheets("Sheet1").Range("K" & i).PasteSpecial _
        Paste:=xlPasteValidation
        End If
    Next i

End Sub