Macro to delete rows or fill them wiht N/A

[Cdworks]

Here is a link showing what I have so far. I need Blanks to be filled or removed in order to use autofilter:

http://screencast.com/t/ViLLHhLrb97a

I will also need a macro that deletes any info past a certain date.

 

[Cdworks]

Here is the entire code, I had to combine everything so the people that will be using this will be able to run it.

I added a conditional format section and it is spitting out this error :
"Run-time error '1004':

The command you chose cannot be performed with multiple selections.

Select a single range and click the command again."

Sub CDW()
'
' CDW Macro
' Macro recorded 6/1/2011 by Cody D Works
'
'
    Range("A:E,G:G,I:I,K:K,M:M,O:O").Select
    Range("O1").Activate
    ActiveWindow.SmallScroll ToRight:=14
    Range("A:E,G:G,I:I,K:K,M:M,O:O,Q:Q,S:S,U:U,W:W,Y:Y,AA:AA").Select
    Range("AA1").Activate
    Selection.Delete Shift:=xlToLeft
 
    Dim rngs As Range
 
    Set rngs = Range(Range("A2"), Range("A65536").End(xlUp))
    rngs.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 
 
    Dim rng As Range, cell As Range
    Dim delRng As Range
    Dim dateCol As String
    Dim cutoffDate As Date
    Dim TheString As String
TheString = Application.InputBox("Enter A Date")
If IsDate(TheString) Then
    cutoffDate = DateValue(TheString)
Else
    MsgBox "Invalid date"
 
End If
 
    dateCol = "E"
        Set rng = Range(Range(dateCol & "2"), Range(dateCol & "65536").End(xlUp))
 
    For Each cell In rng
        If IsDate(cell.Value) And cell.Value > cutoffDate Then
            If delRng Is Nothing Then
            Set delRng = cell
            Else
            Set delRng = Union(delRng, cell)
            End If
        End If
    Next cell
    If Not delRng Is Nothing Then delRng.EntireRow.Delete
 
Selection.AutoFilter
    Range("A1:L127").Sort Key1:=Range("E1"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Columns("H:H").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
        Formula1:="0"
    With Selection.FormatConditions(1).Font
        .Bold = True
        .Italic = False
        .Underline = xlUnderlineStyleSingle
    End With
    Selection.FormatConditions(1).Interior.ColorIndex = 6
 
End Sub

 

[Ralajer]

The selection is still on
Range("A:E,G:G,I:I,K:K,M:M,O:O,Q:Q,S:S,U:U,W:W,Y:Y,AA:AA").Select

and the auto filter does not work and a range with multiple areas aka discontinuous. I am assuming that you want to autofilter on the A1:L127 range instead. I removed all the select methods as you don't really need them and called the other sub rather than including them in the code.

Sub CDW()
'
' CDW Macro
' Macro recorded 6/1/2011 by Cody D Works
'
    Dim sortRng As range
    Dim cFormatRng As range

    range("A:E,G:G,I:I,K:K,M:M,O:O,Q:Q,S:S,U:U,W:W,Y:Y,AA:AA").Delete Shift:=xlToLeft

    '// Call Subs from main rather than including there code in main's code
    DeleteEmptyRows
    DeleteRowAfterDate
    
    Set sortRng = range("A1:L127")
    
    sortRng.AutoFilter
    sortRng.Sort Key1:=range("E1"), Order1:=xlAscending, Header:=xlGuess, _
                 OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal
                
    Set cFormatRng = Columns("H:H")
    cFormatRng.FormatConditions.Delete
    cFormatRng.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="0"
    
    With cFormatRng.FormatConditions(1)
        .Font.Bold = True
        .Font.Italic = False
        .Font.Underline = xlUnderlineStyleSingle
        .Interior.ColorIndex = 6
    End With
End Sub

Sub DeleteEmptyRows()
    Dim rng As range
    
    Set rng = range(range("A2"), range("A65536").End(xlUp))
    '// Delete entire row of cells in range if they are Blank
    If Not rng Is Nothing Then
        rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End If
End Sub

Private Sub DeleteRowAfterDate()

    Dim rng As range, cell As range
    Dim delRng As range
    Dim dateCol As String
    Dim cutoffDate As Date
    Dim TheString As String
    
    TheString = Application.InputBox("Enter A Date")
    If IsDate(TheString) Then
        cutoffDate = DateValue(TheString)
    Else
        MsgBox "Invalid date"
     
    End If

   dateCol = "E"
       Set rng = range(range(dateCol & "2"), range(dateCol & "65536").End(xlUp))

   For Each cell In rng
       If IsDate(cell.Value) And cell.Value > cutoffDate Then
           If delRng Is Nothing Then
           Set delRng = cell
           Else
           Set delRng = Union(delRng, cell)
           End If
       End If
   Next cell
   If Not delRng Is Nothing Then delRng.EntireRow.Delete
End Sub

Don't have your data set to test against so let me know if it works.

 

[Cdworks]

That worked had to adjust the range to A2:L127 i htink i need to increase that incase i have more rows than 127.

Thank you so much!