0

I was wondering how I can delete blank rows from a table using vba. Any help would be appreciated! The code I currently have will create table just fine, and it will select the range I need to delete. But I can't figure out how to actually get it to delete the rows I need. There are two different tables I need to do this for.

Thanks in advance!

Worksheets.Item("Report").Range("H1:L50").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$H$1:$L$150"), ,xlYes).Name  = _
"Table1"

Range("Table1").Activate

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
Alec Terry
  • 37
  • 1
  • 6
  • What do you intend to happen when a cell is blank, but other cells on the row are non-blank? (`Selection.SpecialCells(xlCellTypeBlanks).Select`, which is [I think] the line you say is correctly selecting the cells to delete, won't discriminate between rows which are entirely blank and rows where there are some blanks and some non-blanks.) – YowE3K Sep 14 '16 at 21:14
  • It's best practice to avoid [`.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) - that may be causing issues, since you select something, then activate something after. – BruceWayne Sep 14 '16 at 21:14

1 Answers1

1

I would delete the empty rows before I convert the range to a table.

Dim Target As Range
Set Target = Range("$H$1:$L$150")
Target.SpecialCells(xlCellTypeBlanks).Delete xlShiftUp

ActiveSheet.ListObjects.Add(xlSrcRange, Target, , xlYes).Name = "Table1"