Code to remove 'NULL' values

Question

Let me give a quick layout what our process is:

I export a report into Excel (Let's call this workbook "Raw Data"). I run an Extract macro on the imported file:

Sub Extract_Sort_1601_January()
'
Dim ANS As Long

ANS = MsgBox("Is the January 2016 Swivel Master File checked out of SharePoint and currently open on this desktop?", vbYesNo + vbQuestion + vbDefaultButton1, "Master File Open")
If ANS = vbNo Or IsWBOpen("Swivel - Master - January 2016") = False Then
    MsgBox "The required workbook is not currently open. Please open the correct file and restart the Extract process. This procedure will now terminate.", vbOKOnly + vbExclamation, "Terminate Procedure"
    Exit Sub
End If

    Cells.EntireRow.Hidden = False

Application.ScreenUpdating = False

    ' This line autofits the columns C, D, O, and P
    Range("C:C,D:D,O:O,P:P").Columns.AutoFit

Dim LR As Long

    For LR = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
        If Range("B" & LR).Value <> "1" Then
            Rows(LR).EntireRow.Delete
        End If
    Next LR

With ActiveWorkbook.Worksheets("Extract").Sort
    With .SortFields
        .Clear
        .Add Key:=Range("B2:B2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add Key:=Range("D2:D2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add Key:=Range("O2:O2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add Key:=Range("J2:J2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add Key:=Range("K2:K2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Add Key:=Range("L2:L2000"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
    .SetRange Range("A2:Z2000")
    .Apply
End With
Cells.WrapText = False
Sheets("Extract").Range("A2").Select

    Dim LastRow As Integer, i As Integer, erow As Integer

    LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Cells(i, 2) = "1" Then

            ' As opposed to selecting the cells, this will copy them directly
            Range(Cells(i, 1), Cells(i, 26)).Copy

            ' As opposed to "Activating" the workbook, and selecting the sheet, this will paste the cells directly
            With Workbooks("Swivel - Master - January 2016.xlsm").Sheets("Swivel")
                erow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                .Cells(erow, 1).PasteSpecial xlPasteAll
            End With
            Application.CutCopyMode = False
        End If
    Next i

Application.ScreenUpdating = True
End Sub

This copies data from the "extract" file into another workbook (This workbook is called "Swivel"). This part completes successfully. Once this is completed, in the "Swivel" workbook, we then run a remove duplicates macro:

Sub Remove_Duplicates()
'
Application.ScreenUpdating = False

    ActiveSheet.Range("$A$1:$Z$2000").RemoveDuplicates Columns:=Array(10, 11, 12, 13, 14, 15, 16), Header:=xlYes
    ActiveWindow.SmallScroll Down:=6

Range("C" & Rows.Count).End(xlUp).Offset(1).Select

Application.ScreenUpdating = True

End Sub

Somewhere between the copying of data into the 'Swivel' workbook and running the Remove Duplicates macro, there is a null value (I think) inserted into the cells in column AD in the rows just pasted in. I only know this because this code is running in the worksheet for changes:

Private Sub Worksheet_Change(ByVal Target As Range)
'
Dim r As Range
Set r = Target.EntireRow

    If Target.row = 1 Then Exit Sub ' Don’t change header color

    If r.Cells(1, "AD").Value <> "" Then 
        r.Font.Color = RGB(0, 176, 80)
    Else
        r.Font.ColorIndex = 1
    End If
End Sub

For clarification, here is where the above subs reside:

Extract_Sort_1601_January is part of an Add-in I created for the "raw data" file.

Remove_Duplicates is in a module in the "Swivel" workbook.

WorkSheet_Change is in the Sheet1 object in the "Swivel" workbook.

1. Data from the reporting site is exported to the "raw data" workbook
2. Extract_Sort_1601_January copies data into the existing "Swivel" workbook (In this case that workbook name is "Swivel - Master - January 2016.xlsm")
3. Remove_Duplicates is initiated on the "Swivel" workbook.

If there is no data in column AD of the "Swivel" workbook, the text in that row should be black. However, that is not the case after running the Remove Duplicates macro, the text is green. If I go to the 'empty' cell (column AD) in that row and click delete, then the row changes to black text. I also checked to see if there is a space in the cell, but there is not. How do I code the removal of this 'null' value that is making the Worksheet Change sub believe there is a value in the cell? And, can this be added to the 'Remove Duplicates' sub?

Thanks for all the assistance!

Answer 1

test this code:

Sub test()

Dim LastRow As Long
dim i as long
LastRow = 100 'change this to the last row (if it work)
Application.EnableEvents = True

 For i = 2 To LastRow
  If Trim(Range("AD" & i).Value) = "" Then Range("AD" & i).ClearContents
 Next

End Sub