Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-23-2017, 01:55 AM
enuff enuff is offline how to delete every blank and non-numeric rows without removing the header Windows 7 64bit how to delete every blank and non-numeric rows without removing the header Office 2013
Novice
how to delete every blank and non-numeric rows without removing the header
 
Join Date: Jul 2015
Posts: 6
enuff is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 08-23-2017, 06:53 AM
NoSparks NoSparks is offline how to delete every blank and non-numeric rows without removing the header Windows 7 64bit how to delete every blank and non-numeric rows without removing the header Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
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
  #3  
Old 08-23-2017, 11:33 PM
enuff enuff is offline how to delete every blank and non-numeric rows without removing the header Windows 7 64bit how to delete every blank and non-numeric rows without removing the header Office 2013
Novice
how to delete every blank and non-numeric rows without removing the header
 
Join Date: Jul 2015
Posts: 6
enuff is on a distinguished road
Default

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!
Reply With Quote
  #4  
Old 08-24-2017, 05:56 AM
NoSparks NoSparks is offline how to delete every blank and non-numeric rows without removing the header Windows 7 64bit how to delete every blank and non-numeric rows without removing the header Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

There's no such thing as Columns("AL2:AL2000")
but there is a Range("AL2:AL2000")
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to delete every blank and non-numeric rows without removing the header Delete blank rows between the two rows that contain data beginner Excel Programming 5 12-26-2014 12:29 AM
how to delete every blank and non-numeric rows without removing the header 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
how to delete every blank and non-numeric rows without removing the header Delete Blank Rows (Cyrillic Text in Spreadsheet ) dozd Excel 1 02-22-2013 03:24 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:30 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft