sort and delete

S

SMILE

Hi everyone
Someone please helpme......
I have an excel file with some data and the column "K" is to enter th
date of payment.
I want the data to be sorted by column "K" and delete the rows if i
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
Tom
 
D

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

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

SMILE

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

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