Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if certain column of that row doesn't contain value
I need macro that would delete enire row if column D doesn't contain value
For example I have folowing worksheet: A B C D 1 xx ee tt gg 2 3 ss aa dd 4 ss 5 11 33 55 66 After running macro this sheet should look like this A B C D 1 xx ee tt gg 2 ss aa dd 3 11 33 55 66 Row's 2 and 5 are deleted becouse cells in column D where empty for those rows. Row's 3 and 5 are shifted up I think this would do the trick for 1st row. Now I need to add if statement that would determine which rows to delete ? Rows("1").Delete Shift:=xlUp |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if certain column of that row doesn't contain value
Hi Mikus,
Try: '=========================== Public Sub Tester03() Dim rng As Range Set rng = Intersect(ActiveSheet.UsedRange, Columns("D:D")) On Error Resume Next rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delet e On Error GoTo 0 End Sub '=========================== Or use the built in autofilter feature. --- Regards, Norman "Mikus" wrote in message ... I need macro that would delete enire row if column D doesn't contain value For example I have folowing worksheet: A B C D 1 xx ee tt gg 2 3 ss aa dd 4 ss 5 11 33 55 66 After running macro this sheet should look like this A B C D 1 xx ee tt gg 2 ss aa dd 3 11 33 55 66 Row's 2 and 5 are deleted becouse cells in column D where empty for those rows. Row's 3 and 5 are shifted up I think this would do the trick for 1st row. Now I need to add if statement that would determine which rows to delete ? Rows("1").Delete Shift:=xlUp |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if certain column of that row doesn't contain value
Thank you Norman, this works flawlessly
"Norman Jones" wrote: Hi Mikus, Try: '=========================== Public Sub Tester03() Dim rng As Range Set rng = Intersect(ActiveSheet.UsedRange, Columns("D:D")) On Error Resume Next rng.SpecialCells(xlCellTypeBlanks).EntireRow.Delet e On Error GoTo 0 End Sub '=========================== Or use the built in autofilter feature. --- Regards, Norman "Mikus" wrote in message ... I need macro that would delete enire row if column D doesn't contain value For example I have folowing worksheet: A B C D 1 xx ee tt gg 2 3 ss aa dd 4 ss 5 11 33 55 66 After running macro this sheet should look like this A B C D 1 xx ee tt gg 2 ss aa dd 3 11 33 55 66 Row's 2 and 5 are deleted becouse cells in column D where empty for those rows. Row's 3 and 5 are shifted up I think this would do the trick for 1st row. Now I need to add if statement that would determine which rows to delete ? Rows("1").Delete Shift:=xlUp |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete row if certain column of that row doesn't contain value
Thanks Norman! -- Djmask ------------------------------------------------------------------------ Djmask's Profile: http://www.excelforum.com/member.php...o&userid=24414 View this thread: http://www.excelforum.com/showthread...hreadid=396801 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy column header to next column, delete & delete every nth colum | New Users to Excel | |||
delete cells column. Delete empty cells | Excel Worksheet Functions | |||
Look for value in a column and delete | Excel Programming | |||
Delete All Rows That Column A value is not in Column A of Sheet2 | Excel Programming | |||
Delete row when zero in column b thru e | Excel Programming |