sort and delete

[SMILE]

Hi everyone
Someone please helpme......
I have an excel file with some data and the column "K" is to enter the date of payment.
I want the data to be sorted by column "K" and delete the rows if it has a data in column "K"

This is to find out the rows with no date in "K"

I need a macro to run

Thanks in Advance
Toms

[Dave Peterson]

How about an alternative.

Select your range (all of it or just column K).

Then use Data|Filter|Autofilter

You can use the dropdown arrow in column K to show just the blanks.

Or you can use that dropdown to show all the non-blanks (and delete those
visible rows if you really want).

If you need a macro, record one when you do it manually.

I really like keeping the data and hiding/showing the data.



SMILE wrote:
>
> Hi everyone
> Someone please helpme......
> I have an excel file with some data and the column "K" is to enter the
> date of payment.
> I want the data to be sorted by column "K" and delete the rows if it
> has a data in column "K"
>
> This is to find out the rows with no date in "K"
>
> I need a macro to run
>
> Thanks in Advance
> Toms
>
> --
> SMILE
> ------------------------------------------------------------------------
> SMILE's Profile: http://www.excelforum.com/member.php...fo&userid=4880
> View this thread: http://www.excelforum.com/showthread...hreadid=390143

--

Dave Peterson

[swatsp0p]

While I'm sure a macro can be built to do what you ask, have you looked at AutoFilter? You can apply the filter to show only rows with blanks in Col. K (or only non-blanks) which you can then delete enmasse or copy to another area, etc.

Just a thought....

[SMILE]

Hi
I do agree the autofilter will do.. but in my case I do not want the autofilter.. I really need a macro to run. I cannot record a new macro doing it mannual becoz each time the number of rows will be different. Hope someone can help me.....
Thanks

[Dave Peterson]

I recorded a macro that selected column K, did the autofilter, showed the
non-blank cells and deleted those visible rows and removed the autofilter.

Option Explicit
Sub Macro1()
Columns("K:K").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>"
Range("K2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Range("K1").Select
Selection.AutoFilter
End Sub

It seemed to work fine no matter the number of rows.

SMILE wrote:
>
> Hi
> I do agree the autofilter will do.. but in my case I do not want the
> autofilter.. I really need a macro to run. I cannot record a new macro
> doing it mannual becoz each time the number of rows will be different.
> Hope someone can help me.....
> Thanks
>
> --
> SMILE
> ------------------------------------------------------------------------
> SMILE's Profile: http://www.excelforum.com/member.php...fo&userid=4880
> View this thread: http://www.excelforum.com/showthread...hreadid=390143

--

Dave Peterson