Macro to Delete Blanks in Col and Move Remainder of Range Up

[Zaidy036]

Win7 and Excel 2007 with ASAP Utilities

Named range in a column with data filled by Paste-Special-Values and
sorted A-Z by a Macro.

All cells are text. Some cells are blank and sort to the top of the range.

I would like to delete the blank cells and have the remaining values
moved up but cannot write the required Macro.

Any suggestions would be appreciated.

Eric

 

[Don Guillett]

Try recording it and if you still can't get it post back with ALL of your
code.

 

[Zaidy036]

Try recording it and if you still can't get it post back with ALL of
your code.

I had tried various approaches before posting but I have now solved the
problem.

It seems that the Macro recorder does not record the use of add-ins even
though add-ins do operate during mouse recording.

Going into edit and adding:
Application.Run "'ASAP Utilities.xla'!ASAPRunProc", 86
trimmed excess spaces before the sort which eliminated the blanks.

Thanks for you offer to help.

Eric

 

[Don Guillett]

For something this simple you didn't need the addin. Perhaps you should
follow my suggestion and post YOUR code for comments.

 

[Dana DeLouis]

Named range in a column ...

I would like to delete the blank cells and have the remaining values
moved up but cannot write the required Macro.

Hi. Here's one way...

Sub Demo()
On Error Resume Next
[MyNamedRange].SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

'Or...
[MyNamedRange].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

= = = =
HTH
Dana DeLouis