How does this VBA code work?

[wonderdunder]

Some kind soul provided me with this sample code which will populate a userform omitting rows where a cell is blank in column C (have attached the sheet).

I'm trying to adapt it to a sheet where there are 22 columns (A - Q), and it is if a cell in column M is blank that the user form should show.

Unfortunately I don't seem to understand the syntax of the code. I think the "Count, 3" bit is throwing me off.

If someone could quickly explain it to me I'd be most appreciative.

Private Sub UserForm_Initialize()
Dim xCell As Range
For Each cell In Range("C2", Cells(Rows.Count, 3).End(xlUp))
If Not IsEmpty(cell) Then Me.CB_RefNo.AddItem cell.Offset(0, -2).Value
Next

End Sub

[JosephP]

For Each cell In Range("M2", Cells(Rows.Count, "M").End(xlUp))

[wonderdunder]

offending 2 words removed. Can I see the "hidden" responses please or shall I go to the the naughty corner?

[JosephP]

For Each cell In Range("M2", Cells(Rows.Count, "M").End(xlUp))

is what I said before...

[royUK]

The title doesn't really describe the problem - something like explain code that loads userform.

However, there's absolutely no point in looping through the range, just refer to all the non blank cells using Specialcells

Private Sub UserForm_Initialize()
    Dim rRng As Range, rCl As Range
    With Sheet1
        On Error Resume Next
        Set rRng = .Range(.Cells(2, 3), .Cells(.Rows.Count, _
            3).End(xlUp)).SpecialCells(xlCellTypeConstants)
        On Error GoTo 0
        For Each rCl In rRng
            Me.CB_RefNo.AddItem Format(rCl.Value, "long date")
        Next rCl
    End With
End Sub

[wonderdunder]

Roy - many thanks. I'm trying to adopt this code so that the drop down box will only show data in column C if the cell in column M of the same row is not blank, but I do not understand the syntax of the code (as per my first post). Can you or someone else explain the code or if not provide the code amendments that will do as I need it to?

[royUK]

Here'an explanation of my coe

Private Sub UserForm_Initialize()
'set the variables
    Dim rRng As Range, rCl As Range
    With Sheet1 '< - use Sheet 1
        On Error Resume Next '<- need to have errror handler in case no cells meet the SpecvialCells criteria
		'R1C1 format Row number, Column number
		'set the range from C2 to last used cell in C
		'Rows.Count will be the last cell in the column & check back to the top
		'to finf the next empty cell
		'SpecialCells(xlCellTypeConstants) will pick only cells containing Constants
        Set rRng = .Range(.Cells(2, 3), .Cells(.Rows.Count, _
            3).End(xlUp)).SpecialCells(xlCellTypeConstants)
        On Error GoTo 0
		'loop through those cells & format & add to ComboBox
        For Each rCl In rRng
            Me.CB_RefNo.AddItem Format(rCl.Value, "long date")
        Next rCl
    End With
End Sub