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

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
0
Zaidy036
1/15/2010 10:58:31 PM
excel 39879 articles. 2 followers. Follow

4 Replies
1250 Views

Similar Articles

[PageSpeed] 54

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

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Zaidy036" <ericNOSPAM@bloch.com> wrote in message 
news:hiqrv0$re0$1@news.eternal-september.org...
> 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 

0
Don
1/15/2010 11:25:15 PM
On 1/15/2010 6:25 PM, Don Guillett wrote:
> 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
0
Zaidy036
1/16/2010 3:45:38 AM
For something this simple you didn't need the addin. Perhaps you should 
follow my suggestion and post YOUR code for comments.

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Zaidy036" <ericNOSPAM@bloch.com> wrote in message 
news:hircpb$qna$1@news.eternal-september.org...
> On 1/15/2010 6:25 PM, Don Guillett wrote:
>> 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 

0
Don
1/16/2010 1:01:11 PM
 > 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



On 1/15/10 5:58 PM, Zaidy036 wrote:
> 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
0
Dana
1/16/2010 1:24:51 PM
Reply:

Similar Artilces: