I want to sample a cell in Excel and if it is empty, I want to delete that row. I can delete a row using the row number but can't do it using a variable. Example.. for row = 1 to 5 if activeheet.cells(row,3).value = "" then rows(row:row).select 'range selection.delete shift:= xlUp end if next row This brings up a compiler error. If I use and actual row number than its ok. Any suggestions Thanks..Gary

The following works in Excel 97. Before running the Macro you must select the range of cells within the column you are working with and then run the Macro. Sub DeleteRowsBlank() ' ' DeleteRowsBlank Macro ' Macro recorded 12/04/2003 ' ' Selection.SpecialCells(xlCellTypeBlanks).Select Selection.EntireRow.Delete Range("A1").Select End Sub HTH Bryan

First, check your spelling in line 2! Then also read what compiler is telling you - you don't use a value:value for Row, it is a single reference (see line 3). The below sample works: For Row = 1 To 5 If ActiveSheet.Cells(Row, 3).Value = "" Then Rows(Row).Delete shift:=xlUp End If Next Row

Bryan. Thanks. it did just what I was looking for. Now I have another question. How can I do the same thing looking for cells containing specific data such as "sold". I tried (xlcelltypecomments, "sold") but that didn't work. Thanks again..Gary

I pass, Bryan can have this one. :)

If the only text string is "Sold" then you can use: Sub DeleteRowsSold() ' ' DeleteRowsBlank Macro ' Macro recorded 12/04/2003 ' ' Selection.SpecialCells(xlCellTypeConstants, xlTextValues).Select Selection.EntireRow.Delete Range("A1").Select End Sub I am unable to specify just "Sold". (Lack of knowledge) Bryan

From Excel VBA Help: Syntax expression.SpecialCells(Type, Value) expression Required. An expression that returns a Range object. Type Required Long. The cells to include. Can be one of the following XlCellType constants. Constant Description xlCellTypeNotes Cells containing notes xlCellTypeConstants Cells containing constants xlCellTypeFormulas Cells containing formulas xlCellTypeBlanks Empty cells xlCellTypeLastCell The last cell in the used range xlCellTypeVisible All visible cells Value Optional Variant. If Type is either xlCellTypeConstants or xlCellTypeFormulas, this argument is used to determine which types of cells to include in the result. These values can be added together to return more than one type. The default is to select all constants or formulas, no matter what the type. Can be one of the following XlSpecialCellsValues constants: xlErrors, xlLogical, xlNumbers, xlTextValues, xlAllFormatConditions, or xlSameFormatConditions.