Delete Blank Cells and shift cells up code not working

[bobbyexcel]

Can you please help me on the below code to Delete Blank Cells and shift cells up.

Tried the below 2 but not working.. I still have blank cells which are not getting deleted.

Columns("F").SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp

With Range("A:J") ' & Lastrow2)
If WorksheetFunction.CountA(.Cells) > 0 Then .SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftUp
End With

 

[Jon von der Heyden]

Can you please check one of the lines that is not being deleted, to see if it is truly blank. 2 tests as follows:

=ISBLANK(A1)
=LEN(A1)

Please amend cell reference as necessary and please report back on the results.

 

[bobbyexcel]

thanks for the heads-up.

ISBLANK result is coming as False but Length is zero.

 

[Jon von der Heyden]

Ok that implies that these cells hold nullstrings. The SpecialCells method will not work. Instead you need to loop through your rows, from bottom to top and then delete the rows based on whether or not the values have zero character length.

Are you OK with that or do you need help?

 

[Fluff]

If you have copy/pasted as values the data try

With Range("F2", Range("F" & Rows.Count).End(xlUp))
   .Value = .Value
   .SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
End With

 

[Jon von der Heyden]

.Value = .Value

I really didn't expect that to work - but it does! Wow!

 

[Fluff]

It's something I picked up, from a thread on here, some years ago.

 

[bobbyexcel]

Yes it worked very well. Thanks for your help. .You saved my day..

If you have copy/pasted as values the data try

With Range("F2", Range("F" & Rows.Count).End(xlUp))
   .Value = .Value
   .SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
End With

 

[Fluff]

Glad we could help & thanks for the feedback