How to delete blank rows table rows in vba

Question

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

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
– YowE3K, Commented Sep 14, 2016 at 21:14
It's best practice to avoid .Select - that may be causing issues, since you select something, then activate something after. — BruceWayne
– BruceWayne, Commented Sep 14, 2016 at 21:14

user6432984 · Accepted Answer · 2016-09-14 21:21:25Z

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"

answered Sep 14, 2016 at 21:21

user6432984

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to delete blank rows table rows in vba

1 Answer 1

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

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

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

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related