If Statement in vba to Delete a Line

mbesspiata · #1 04-01-2014, 10:05 AM

I run a report weekly that sometimes exports a blank line in the pivot table and therefore the last line of the table in column A has this (blank). I would like to create something in vba to look in column A for a cell that contains (blank) and then delete that line from the sheet.

Is that possible?

Mike

macropod · #2 04-01-2014, 06:11 PM

You could use a macro like:

Code:

Sub DeleteBlanks()
Dim lRow As Long
With ThisWorkbook.ActiveSheet.UsedRange
  lRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
  .Range("A1:A" & lRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

mbesspiata · #3 04-02-2014, 04:07 AM

Thanks Paul. One thing though, when I put (blank) I meant that is the actual characters showing in that cell. So it's not just a blank cell, it's a cell with the word (blank) (with the parentheses) in it.

Mike

macropod · #4 04-02-2014, 03:11 PM

OK, try:

Code:

Sub Demo()
Dim iRow As Long
With ActiveSheet.UsedRange
  For iRow = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
    If .Range("A" & iRow).Value = "(blank)" Then
      .Rows(iRow).EntireRow.Delete
    End If
  Next iRow
End With
End Sub

mbesspiata · #5 04-08-2014, 04:32 AM

Thanks so much for your help. Perfect.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Why cant I delete the black line at the side of my orginisational chart?	Gabby1991	Drawing and Graphics	1	01-07-2014 09:14 PM
unable to delete a horizontal line	jerryny	Word	2	03-31-2013 05:59 PM
Search for text then delete line	konopca	Word VBA	2	06-07-2012 02:44 AM
Very Urgent - Not able to delete Line	krishnaoptif	Drawing and Graphics	11	05-29-2012 11:25 AM
Cannot delete line	adkr	Office	1	04-07-2010 09:26 PM