sort and delete
 

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

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

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....


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=390143

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

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