Since you have counted empty cells, you can specify from which row of the table data to insert empty rows (the result from cell L1 +1) and expand the range to five (Resize(5) ).
After recording the macro, it is a good idea to get rid of all the Selects from the code by modifying the code accordingly.
Sub Reformat_Fprints_Mail_List_Art()
Dim LO As ListObject
' Reformat_Fprints_Mail_List Macro
'
' Keyboard Shortcut: Ctrl+Shift+F
'
Columns("M:O").Delete Shift:=xlToLeft
Rows("1:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Set LO = ActiveSheet.ListObjects.Add(xlSrcRange, Range("A4").CurrentRegion, , xlYes)
With LO
.Name = "Table1"
.ShowAutoFilterDropDown = False
.TableStyle = ""
With .Sort
.SortFields.Clear
.SortFields.Add2 Key:=LO.HeaderRowRange(12)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Columns("A").ColumnWidth = 5
Columns("B").ColumnWidth = 8
Columns("C:D").ColumnWidth = 14
Columns("E").ColumnWidth = 27
Range("F:F,H:H").ColumnWidth = 16
Columns("G").ColumnWidth = 20
Columns("I").ColumnWidth = 11
Columns("J").ColumnWidth = 12
Columns("K").EntireColumn.AutoFit
Columns("L").ColumnWidth = 8
LO.HeaderRowRange(1).Value = "Mem Num"
LO.HeaderRowRange.WrapText = True
Range("A1").Value = "Vol"
Range("A1:B1").Style = "Heading 1"
Range("K1:L1").Value = Array("UK Post", "=COUNTBLANK(Table1[F-By])")
Range("K2:L2").Value = Array("Air", "=COUNTIF(Table1[F-By],""Air"")")
Range("K3:L3").Value = Array("emag", "=COUNTIF(Table1[F-By],""emag"")")
Range("K1:L3").HorizontalAlignment = xlRight
'Insert 5 empty rows at the end of the "blank" category
LO.ListRows(Range("L1").Value + 1).Range.Resize(5).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub
Artik
Bookmarks