Delete line in a Table when the the cell is empty

[Romano_odK]

Good morning,

Is it possible to delete a whole line with VBA when in my case in column W the values in the cells starting from line 7 until the end are empty with the push of a button?

Thank you for your time?

Romano

 

[bebo021999]

How to identify which row in column W is the last row?
We need a screenshot/image of worksheet to see a larger picture.

 

[Jimmypop]

Hi...

So are you wanting to delete the entire row if the rows starting from row 7 in Column W are empty?

 

[Romano_odK]

Hi...

So are you wanting to delete the entire row if the rows starting from row 7 in Column W are empty?

The table rows start at row 7 and down to the end, so I need every row to be deleted when the cell on the same row in the column W is empty. Can you do this?

 

[bebo021999]

This will delete entirerow till row 10000

 range("W7:W10000").specialcells(xlcelltypeblanks).entirerow.delete

 

[Romano_odK]

This will delete entirerow till row 10000

 range("W7:W10000").specialcells(xlcelltypeblanks).entirerow.delete

Thank you for your help. Unfortunately it doesn't seem to work in my table. "Delete method of Range class failed". What can be the cause of this?

 

[Jimmypop]

Is your data inside a Named Table e.g. Table1 or is it a normal range?

 

[Romano_odK]

Is your data inside a Named Table e.g. Table1 or is it a normal range?

A named one, "Table_Query_from_A100"

 

[Jimmypop]

Try the below... ( @bebo021999 ) can confirm also if correct....

Sub Del_Blank()
  Dim Rng As Range
  On Error Resume Next
  Set Rng = Range("Table_Query_from_A100[[[COLOR=rgb(226, 80, 65)]Column15[/COLOR]]]").SpecialCells(xlCellTypeBlanks) 'Change the text in red to your column header name...e.g. Column15 to whatever your header name is
  On Error GoTo 0
  If Not Rng Is Nothing Then
    Rng.Delete Shift:=xlUp
  End If
End Sub

 

[Jimmypop]

Try the below... ( @bebo021999 ) can confirm also if correct....

Sub Del_Blank()
  Dim Rng As Range
  On Error Resume Next
  Set Rng = Range("Table_Query_from_A100[[[COLOR=rgb(226, 80, 65)]Column15[/COLOR]]]").SpecialCells(xlCellTypeBlanks) 'Change the text in red to your column header name...e.g. Column15 to whatever your header name is
  On Error GoTo 0
  If Not Rng Is Nothing Then
    Rng.Delete Shift:=xlUp
  End If
End Sub

So your code would be like below...

Sub Del_Blank()
  Dim Rng As Range
  On Error Resume Next
  Set Rng = Range("Table1[[YourHeaderNameForColumnW]]").SpecialCells(xlCellTypeBlanks) ' Replace YourHeaderNameForColumnW with your header name
  On Error GoTo 0
  If Not Rng Is Nothing Then
    Rng.Delete Shift:=xlUp
  End If
End Sub