Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
specialcells(xlcelltypeblanks)
Hi,
I have a sheet that I have imported data to, it contains approx 35000 rows and 7 columns. 10000 of these rows either have a empty cell in column A or has text in it and I want to delete the entire row Is there a quicker way then checking each cell in column A and then deleteing that row. Sub CheckForNumber() Dim Lastrow As Long Dim i As Long Lastrow = Cells(65536, 1).End(xlUp).Row + 1 For i = Lastrow To 1 Step -1 Range("a" & i).Activate If Not IsNumeric(ActiveCell.Value) Or IsEmpty(ActiveCell) Then ActiveCell.EntireRow.Delete End If Next End Sub I have tried using the following to delete the empty cells 'On Error Resume Next Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete 'On Error GoTo 0 but it deletes everything. I have used this in another workbook and it works OK. I have also tried filtering the data and only showing the blanks in column A and then deleting visible rows, but I get an error message that it is too complex to do. Thanks in advance Neil |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Work around to SpecialCells(xlCellTypeBlanks)... | Excel Discussion (Misc queries) | |||
Specialcells | Charts and Charting in Excel | |||
VBA ON ERROR does not work with SPECIALCELLS | Excel Discussion (Misc queries) | |||
.SpecialCells(xlLastCell).Select | Excel Programming | |||
specialcells and toolbox button click event | Excel Programming |