Null Validation in Excel Column

To fix the issue you can do I ended coming up with a solution that IMO, is more elegant than @Poweruser 's creating another column, populating it, hiding it, and then using sort on the hidden column. My method utilizes font color changes based on conditional formatting and sorts off of that.
Select desired range of column that contains blank values you want to sort by Use Conditional Formatting>New Rule>'Use a formula to determine which cells to format' and in the textbox use the formula =IF(INDIRECT("RC",0)="",TRUE,FALSE) Select 'Format...', select 'Font' tab and change the Font color to something not black or 'Automatic', apply changes Using 'Sort', have 'Sort By' be the column with the blank cells, 'Sort On' be 'Font Color', and for 'Order By' change Automatic to whatever color you selected and have it be 'On Top'

For oRow = 2 To iFinalRow
    ActiveWorkbook.ActiveSheet.Cells(oRow, 5).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF(INDIRECT(""RC"",0)="""",TRUE,FALSE)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
Next oRow

'Sort
ActiveWorkbook.ActiveSheet.SORT.SortFields.Clear
ActiveWorkbook.ActiveSheet.SORT.SortFields.Add(Range("E:E"), _
    xlSortOnFontColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(31, 73, 125)
ActiveWorkbook.ActiveSheet.SORT.SortFields.Add _
    Key:=Range("D1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
With ActiveWorkbook.ActiveSheet.SORT
    .SetRange Range("A:F")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

seems to work fine Creating, for example, a date validation for one rows of one cell in excel 2010 is no problem. Can I automatically apply this validation rule to all subsequent rows of that column when I create an excel sheet from this template? , You can identify the last row of a column, like so

finalRow = cells(1000000,1).end(xlup).row

for i = 1 to finalRow
  'your validation code here which will refer to the ith row (e.g., cells(i,1))
  'column A = 1; column B = 2, and so on
next i

Hope that helps Consider an XSLT (the declarative programming language that re-structures XML files for end use needs) to remove the @NULL attribute. VBA can use the XSLT processor with the Microsoft XML object. The needed XSLT is very simple and straightforward where you include the identity transform to copy all content (nodes and attributes) as is and then run an empty template match on @NULL to remove attribute wherever it appears in document.
XSLT (embedded below as VBA string)

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output version="1.0" encoding="UTF-8" indent="yes" />

  <!-- Identity transform -->
  <xsl:template match="@*|node()">
    <xsl:copy>
      <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="@NULL">    
  </xsl:template>

</xsl:stylesheet>

Sub TransformXML()

    Dim xmlDoc As Object, xslDoc As Object, newDoc As Object

    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    Set xslDoc = CreateObject("MSXML2.DOMDocument")
    Set newDoc = CreateObject("MSXML2.DOMDocument")

    xmlDoc.Load "Original.xml"
    xmlDoc.async = False            

    ' EMBEDDED XSLT ' 
    xslDoc.LoadXML "<?xml version=" & Chr(34) & "1.0" & Chr(34) & "?>" _
            & "<xsl:stylesheet version=" & Chr(34) & "1.0" & Chr(34) _
            & "                xmlns:xsl=" & Chr(34) & "http://www.w3.org/1999/XSL/Transform" & Chr(34) & ">" _
            & "<xsl:strip-space elements=" & Chr(34) & "*" & Chr(34) & " />" _
            & "<xsl:output method=" & Chr(34) & "xml" & Chr(34) & " indent=" & Chr(34) & "yes" & Chr(34) & "" _
            & "            encoding=" & Chr(34) & "UTF-8" & Chr(34) & "/>" _
            & " <xsl:template match=" & Chr(34) & "node() | @*" & Chr(34) & ">" _
            & "  <xsl:copy>" _
            & "   <xsl:apply-templates select=" & Chr(34) & "node() | @*" & Chr(34) & " />" _
            & "  </xsl:copy>" _
            & " </xsl:template>" _
            & "<xsl:template match=" & Chr(34) & "@NULL" & Chr(34) & ">" _
            & "</xsl:template>" _
            & "</xsl:stylesheet>"

    xslDoc.async = False
    xmlDoc.transformNodeToObject xslDoc, newDoc
    newDoc.Save "OutputXML.xml"    ' READY FOR FINAL IMPORT '        

    MsgBox "Successfully transformed XML!", vbInformation

    Set xmlDoc = Nothing
    Set xslDoc = Nothing
    Set newDoc = Nothing

End Sub

This might help you I am having trouble with getting a data validation list to adjust based on the last column from content in row 5. , This is what i got to work.

Function GetColumnLetter(colNum As Long) As String
    Dim vArr
    vArr = Split(Cells(1, colNum).Address(True, False), "$")
    GetColumnLetter = vArr(0)
End Function

Sub DataRange()
Application.ScreenUpdating = False

Dim startCol As String
Dim startRow As Long
Dim lastCol As Long
Dim myCol As String
Dim rng As Range
Dim cell As Range

Dim sht2 As Worksheet
Set sht2 = ThisWorkbook.Worksheets("Foundation Plates")
Dim sht7 As Worksheet
Set sht7 = ThisWorkbook.Worksheets("Legend")

Call Unprotect
sht2.Activate

startCol = "C"
startRow = 5
lastCol = sht2.Cells(5, sht2.Columns.Count).End(xlToLeft).Column
myCol = GetColumnLetter(lastCol)

Set rng = sht2.Range(startCol & startRow & ":" & myCol & "5")

'For error checking the range
'MsgBox rng.Address

    sht7.Activate
    With sht7.Range("F8").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Operator:=xlBetween, Formula1:="=" & "'" & sht2.Name & "'!" & rng.Address
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With

Call Protect
sht2.Activate

Set sht2 = Nothing
Set sht7 = Nothing
Set rng = Nothing

Application.ScreenUpdating = True
End Sub

should help you out EDIT: Code updated to reflect advice presented in comment. I am currently getting a 1004 error on the "Set RefList" line. , This might be a little better-behaved:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.ScreenUpdating = False

    Dim ws As Worksheet
    Dim RefRng As Range, RngFind As Range, NewRng As Range, hdr
    Dim RefList As Range, c As Range, rngHeaders As Range, Msg

    On Error GoTo ErrHandling

    Set ws = ThisWorkbook.Worksheets("User Picklist")

    'only deal with the selected cell(s)
    Set NewRng = Application.Intersect(Me.Range("A12:T101"), Target)
    If Not NewRng Is Nothing Then

        Set rngHeaders = ws.Range("A11:ZZ11")

        For Each c In NewRng
            c.Validation.Delete 'delete previous validation
            hdr = Me.Cells(11, c.Column).Value
            If Len(hdr) > 0 Then
                Set RngFind = rngHeaders.Find(hdr, , xlValues, xlWhole)
                'matched header?
                If Not RngFind Is Nothing Then

                    Set RefList = ws.Range(RngFind.Offset(1, 0), _
                                           RngFind.Offset(1, 0).End(xlDown))

                    c.Validation.Add Type:=xlValidateList, _
                                     AlertStyle:=xlValidAlertStop, _
                                     Formula1:="='" & ws.Name & "'!" & RefList.Address

                End If 'matched header
            End If 'has header

        Next c
    End If 'in required range

Here:
    Application.ScreenUpdating = True
    Exit Sub

ErrHandling:
    If Err.Number <> 0 Then
        Msg = "Error # " & Str(Err.Number) & " was generated by " & _
            Err.Source & Chr(13) & "Error Line: " & Erl & Chr(13) & Err.Description
        Debug.Print Msg, , "Error", Err.HelpFile, Err.HelpContext
    End If
    Resume Here
End Sub