excel vba - Code to remove 'NULL' values -

let me give quick layout our process is:

i export report excel (let's call workbook "raw data"). run extract macro on imported file:

sub extract_sort_1601_january() ' dim ans long  ans = msgbox("is january 2016 swivel master file checked out of sharepoint , open on desktop?", vbyesno + vbquestion + vbdefaultbutton1, "master file open") if ans = vbno or iswbopen("swivel - master - january 2016") = false     msgbox "the required workbook not open. please open correct file , restart extract process. procedure terminate.", vbokonly + vbexclamation, "terminate procedure"     exit sub end if      cells.entirerow.hidden = false  application.screenupdating = false      ' line autofits columns c, d, o, , p     range("c:c,d:d,o:o,p:p").columns.autofit  dim lr long      lr = range("b" & rows.count).end(xlup).row 2 step -1         if range("b" & lr).value <> "1"             rows(lr).entirerow.delete         end if     next lr  activeworkbook.worksheets("extract").sort     .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     .setrange range("a2:z2000")     .apply end cells.wraptext = false sheets("extract").range("a2").select      dim lastrow integer, integer, erow integer      lastrow = activesheet.range("a" & rows.count).end(xlup).row     = 2 lastrow         if cells(i, 2) = "1"              ' opposed selecting cells, copy them directly             range(cells(i, 1), cells(i, 26)).copy              ' opposed "activating" workbook, , selecting sheet, paste cells directly             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             application.cutcopymode = false         end if     next  application.screenupdating = true end sub 

this copies data "extract" file workbook (this workbook called "swivel"). part completes successfully. once completed, in "swivel" workbook, run 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 copying of data 'swivel' workbook , running remove duplicates macro, there null value (i think) inserted cells in column ad in rows pasted in. know because code running in worksheet changes:

private sub worksheet_change(byval target range) ' dim r range set r = target.entirerow      if target.row = 1 exit sub ' don’t change header color      if r.cells(1, "ad").value <> ""          r.font.color = rgb(0, 176, 80)     else         r.font.colorindex = 1     end if end sub 

for clarification, here above subs reside:

extract_sort_1601_january part of add-in created "raw data" file.

remove_duplicates in module in "swivel" workbook.

worksheet_change in sheet1 object in "swivel" workbook.

1. data reporting site exported "raw data" workbook
2. extract_sort_1601_january copies data existing "swivel" workbook (in case workbook name "swivel - master - january 2016.xlsm")
3. remove_duplicates initiated on "swivel" workbook.

if there no data in column ad of "swivel" workbook, text in row should black. however, not case after running remove duplicates macro, text green. if go 'empty' cell (column ad) in row , click delete, row changes black text. checked see if there space in cell, there not. how code removal of 'null' value making worksheet change sub believe there value in cell? and, can added 'remove duplicates' sub?

thanks assistance!

test code:

sub test()  dim lastrow long dim long lastrow = 100 'change last row (if work) application.enableevents = true   = 2 lastrow   if trim(range("ad" & i).value) = "" range("ad" & i).clearcontents  next  end sub