0

I have a set of data where I'm trying to remove the N/As and shifts the data to the left. Here's my piece of code:

For Each Cell In Range    
 If Cell.Value = "NA" Then 
   Cell.Delete Shift:=xlToLeft
 End If  
Next Cell

However when I have two consecutive NAs, the first NA delete will shift the second NA into the first cell, where the loop is not moving onto the next cell, so as an end result I would have to run the macro multiple times to get all the NAs deleted. I tried to write a Do Until loop on top of it saying something like "do until Range.value <> "NA"", and it's erroring out. What's the best way around this?

Thanks advance for the help!

dott2000
  • 1
  • There are sooooooo many answers on this already. Either loop from the bottom up, or filter and delete the visible, or create a Union Range as you loop and delete as one. – Scott Craner Jun 23 '21 at 22:01
  • Thank yoU Scott! Did the loop from bottom and it worked! – dott2000 Jun 23 '21 at 22:12

0 Answers0