Delete Rows

[kkondrat1]

Hello,

I need to delete all rows in my spreadsheet that contain data in
cell.

here is what it looks like:

ID#..........Date................color
1..............4/01/04...........blue
2..............4/02/04...........yellow
3.....................................purple
4..............4/05/04...........black

I need Visual basic for a macro that would do this

so my end result would be:

ID...........Date..................color
3......................................purple

any ideas

 

[Frank Kabel]

Hi
try the following macro:
Sub delete_rows()
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If IsDate(Cells(row_index, "B").Value) then
Cells(row_index, "B").EntireRow.delete
End If
Next
Application.ScreenUpdating = True
End Sub

 

[Ron de Bruin]

If the cells that look empty are really empty this short macro will work

Sub test()
Columns("B").SpecialCells(xlCellTypeConstants).EntireRow.Delete
End Sub

This code can be a problem
Read this KB
http://support.microsoft.com/?kbid=832293

You can use a loop also
http://www.rondebruin.nl/delete.htm

 

[Ron de Bruin]

I forgot the On error in case there are no constants

Sub test()
On Error Resume Next
Columns("B").SpecialCells(xlCellTypeConstants).EntireRow.Delete
On Error GoTo 0
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl

If the cells that look empty are really empty this short macro will work

Sub test()
Columns("B").SpecialCells(xlCellTypeConstants).EntireRow.Delete
End Sub

This code can be a problem
Read this KB
http://support.microsoft.com/?kbid=832293

You can use a loop also
http://www.rondebruin.nl/delete.htm