Thread: how to delete every blank and non-numeric rows without removing the header
View Single Post
 
Old 08-23-2017, 06:53 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
  
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default
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
Reply With Quote