VBA Table producing too many rows

[CalRich1023]

Hello,

I have a search sheet to loop through my entire workbook and return results into a table-style format. for some reason it is producing 500 rows, but I only want it to produce enough rows for the data that is returned. I will paste the code below, and color the font of where the problem is occurring. The table data is from "A6:K6" and varying amount of rows (depending on search results. Table headers are in "A5:K5" I know I'm missing something small, but I just can't seem to find it. Thanks!

'FORMAT RESULTS AS A TABLE
'BUILD THE TABLE
Dim LastRow1 As Long
LastRow1 = Cells(Rows.Count, "A").End(xlUp).Row
ActiveWorkbook.Sheets("SEARCH").ListObjects.Add(xlSrcRange, Range("A5:K5" & LastRow1), , xlYes) _
.Name = "SEARCH_RESULTS_TABLE"
'COLOR THE TABLE GRAY/WHITE
ActiveWorkbook.Sheets("SEARCH").ListObjects("SEARCH_RESULTS_TABLE").TableStyle = "TableStyleMedium1"
'SHOWS TOTAL QUANTITY AT BOTTOM OF COLUMN "D"
ActiveWorkbook.Sheets("SEARCH").ListObjects("SEARCH_RESULTS_TABLE").ShowTotals = True
ActiveWorkbook.Sheets("SEARCH").ListObjects("SEARCH_RESULTS_TABLE").ListColumns(4) _
.TotalsCalculation = xlTotalsCalculationSum

 

[Michael M]

What happens if you step through then hover over the LastRow1 variable what does it give you
I don't have Excel at the moment but change this line

ActiveWorkbook.Sheets("SEARCH").ListObjects.Add(xlSrcRange, Range("A5:K5" & LastRow1), , xlYes) _

to

ActiveWorkbook.Sheets("SEARCH").ListObjects.Add(xlSrcRange, Range("A5:K" & LastRow1), , xlYes) _

 

[CalRich1023]

What happens if you step through then hover over the LastRow1 variable what does it give you
I don't have Excel at the moment but change this line

ActiveWorkbook.Sheets("SEARCH").ListObjects.Add(xlSrcRange, Range("A5:K5" & LastRow1), , xlYes) _

to

ActiveWorkbook.Sheets("SEARCH").ListObjects.Add(xlSrcRange, Range("A5:K" & LastRow1), , xlYes) _

This small change made the difference. Thank you! I swear I tried this before posting here, but maybe I just imagined it HAHA.
Thanks again for your help!!

 

[Michael M]

Glad to help?