Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting up some sort of cutting and pasting loop | Excel Worksheet Functions | |||
Sort a Subtotalled List? | Excel Discussion (Misc queries) | |||
Select rows, then sort | Excel Discussion (Misc queries) | |||
How do I delete dublicate cells? | Excel Discussion (Misc queries) | |||
how do I reset the default sort order in excel xp back to blanks . | Excel Discussion (Misc queries) |