how to delete every blank and non-numeric rows without removing the header

NoSparks · #2 08-23-2017, 06:53 AM

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

and specify the range "AL2:AL" & LR

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

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