I'm taking data collected from a userform into an array and using the array values to populate columns in a table. I'm finding that additional 'empty data' rows are being created after the array values have been properly populated to the column. I think it has something to do with the 'x' variable count + 1 I am using but not sure how to fix it.
Source Data
Userform collected aryResult (1,7,10,11,12,13,14)
longRowQty = UBound(aryResult) - LBound(aryResult)
Code
Sub Insertrows()
Dim CellTable As ListObject
Dim x As Long, i As Long
Dim ws As Worksheet
Set ws = Worksheets("Cells")
Set CellTable = ws.ListObjects("Celltable")
CellBlockDetails.Show
ResizeTable
x = ws.ListObjects("Celltable").DataBodyRange.Rows.Count 'Current table rows count
Range(CellTable.DataBodyRange.Cells(x - longRowQty + 1, CellTable.ListColumns("Centre Code").Index), _
CellTable.DataBodyRange.Cells(x, CellTable.ListColumns("Centre Code").Index)) = strCentreCombo
Range(CellTable.DataBodyRange.Cells(x - longRowQty + 1, CellTable.ListColumns("Cell Block Name").Index), _
CellTable.DataBodyRange.Cells(x, CellTable.ListColumns("Cell Block Name").Index)) = strCellBlock
Range(CellTable.DataBodyRange.Cells(x - longRowQty + 1, CellTable.ListColumns("Construction").Index), _
CellTable.DataBodyRange.Cells(x, CellTable.ListColumns("Construction").Index)) = strCellorBunk
For i = LBound(aryResult()) To UBound(aryResult())
Range(CellTable.DataBodyRange.Cells(x - longRowQty + 1, CellTable.ListColumns("Cell No.").Index), _
CellTable.DataBodyRange.Cells(x, CellTable.ListColumns("Cell No.").Index)) = aryResult(i)
x = x + 1
Next
End Sub
Sub ResizeTable()
Dim x, y, s, e As Long
With Worksheets("Cells").ListObjects("Celltable")
x = .DataBodyRange.Rows.Count 'Current table rows count
y = .DataBodyRange.Columns.Count - 1 'Current table columns count
s = .Range.Cells(1).Row 'Current table start row
e = .Range.Cells(1).Column 'Current table start column
.Resize Range(Cells(s, e), Cells(s + x + longRowQty, e + y).Address) 'Resize table
End With
End Sub
Table being populated
Source Data
Userform collected aryResult (1,7,10,11,12,13,14)
longRowQty = UBound(aryResult) - LBound(aryResult)
Code
Sub Insertrows()
Dim CellTable As ListObject
Dim x As Long, i As Long
Dim ws As Worksheet
Set ws = Worksheets("Cells")
Set CellTable = ws.ListObjects("Celltable")
CellBlockDetails.Show
ResizeTable
x = ws.ListObjects("Celltable").DataBodyRange.Rows.Count 'Current table rows count
Range(CellTable.DataBodyRange.Cells(x - longRowQty + 1, CellTable.ListColumns("Centre Code").Index), _
CellTable.DataBodyRange.Cells(x, CellTable.ListColumns("Centre Code").Index)) = strCentreCombo
Range(CellTable.DataBodyRange.Cells(x - longRowQty + 1, CellTable.ListColumns("Cell Block Name").Index), _
CellTable.DataBodyRange.Cells(x, CellTable.ListColumns("Cell Block Name").Index)) = strCellBlock
Range(CellTable.DataBodyRange.Cells(x - longRowQty + 1, CellTable.ListColumns("Construction").Index), _
CellTable.DataBodyRange.Cells(x, CellTable.ListColumns("Construction").Index)) = strCellorBunk
For i = LBound(aryResult()) To UBound(aryResult())
Range(CellTable.DataBodyRange.Cells(x - longRowQty + 1, CellTable.ListColumns("Cell No.").Index), _
CellTable.DataBodyRange.Cells(x, CellTable.ListColumns("Cell No.").Index)) = aryResult(i)
x = x + 1
Next
End Sub
Sub ResizeTable()
Dim x, y, s, e As Long
With Worksheets("Cells").ListObjects("Celltable")
x = .DataBodyRange.Rows.Count 'Current table rows count
y = .DataBodyRange.Columns.Count - 1 'Current table columns count
s = .Range.Cells(1).Row 'Current table start row
e = .Range.Cells(1).Column 'Current table start column
.Resize Range(Cells(s, e), Cells(s + x + longRowQty, e + y).Address) 'Resize table
End With
End Sub
Table being populated