a function that deletes all rows with no value in a certain column?

dunndealpr

New member
Joined
Jun 6, 2013
Messages
5
Reaction score
0
Points
0
Hey all, I use Excel 2007. Can it be set up to delete all rows in an excel file when there is no value in a certain column? For instance, every row with no value in column H would automatically be deleted?​






 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Try:

Code:
Sub DeleteBlanks()
     Worksheets("Sheet1").Columns(8).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub


where Sheet1 is name of sheet, .Columns(8) means column H (8th column)
 

dunndealpr

New member
Joined
Jun 6, 2013
Messages
5
Reaction score
0
Points
0
Try:

Code:
Sub DeleteBlanks()
     Worksheets("Sheet1").Columns(8).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub


where Sheet1 is name of sheet, .Columns(8) means column H (8th column)


Thanks so much for your time.


Columns A, B, C and D all have data. Column E is the column where if the cell is blank, I want the entire row deleted. How would the code be written in this case? And this is a VBA script, correct? Sorry, I'm kind of slow with Excel.


Thank you again.
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
You said column H initially....

Anyway you just need to change the 8 to 5 (for 5th column, column E).


Code:
Sub DeleteBlanks()
      Worksheets("Sheet1").Columns([COLOR=#ff0000]5[/COLOR]).SpecialCells(xlCellTypeBlanks).EntireRow.Delete 
End Sub

To use this, hit ALT+F11, then go to Insert|Module.

Paste the above code in the VB editor.

Now back in your sheet go to Developer tab, click Macros button, select the DeleteBlanks macro and click Run.
 
Top