|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how to delete every blank and non-numeric rows without removing the header
Hi,
I am using the following code to remove all blank and non-numeric rows in a dataset of a about 1200 rows: Sub DelTest() Columns("AL:AL").SpecialCells(xlCellTypeBlanks).En tireRow.Delete Columns("AL:AL").SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete End Sub In my case column AL contains only numbers. However, this code also deletes the column header because it is text. How can I edit it so it starts from cell AL2 down? |
#2
|
|||
|
|||
Columns("AL:AL") is actually specifying the range "AL1:AL1048576"
You could specify a hard coded range like "AL2:AL2000" Or find the row of the last used cell on the sheet Code:
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row Or use .Resize to reduce the entire column range and then use .Offset to move the specified range down a row Code:
Columns("AL:AL").Resize(Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete |
#3
|
|||
|
|||
I've tried to limit a range
Sub DelTest() Columns("AL2:AL2000").SpecialCells(xlCellTypeBlank s).EntireRow.Delete Columns("AL2:AL2000").SpecialCells(xlCellTypeConst ants, xlTextValues).EntireRow.Delete End Sub but it gives me an error. This one, however, works and does the job perfectly: Sub DelTest() Columns("AL:AL").Resize(Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeBlanks).Entire Row.Delete Columns("AL:AL").Resize(Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete End Sub Thanks! |
#4
|
|||
|
|||
There's no such thing as Columns("AL2:AL2000")
but there is a Range("AL2:AL2000") |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Delete blank rows between the two rows that contain data | beginner | Excel Programming | 5 | 12-26-2014 12:29 AM |
Delete All empty Rows - Print - Undo all Rows deleted | Bathroth | Word VBA | 1 | 10-01-2014 01:40 PM |
Removing blank pages at the end of a document | Xanzia | Word | 3 | 06-01-2014 11:13 AM |
first character NOT Alpha numeric or Tilde (~) delete. | ksridh | Word VBA | 5 | 03-17-2014 12:45 AM |
Delete Blank Rows (Cyrillic Text in Spreadsheet ) | dozd | Excel | 1 | 02-22-2013 03:24 AM |