Hi all,
This is probably an easy solution but this is my first attempt at writing a macro.
I have a simple spreadsheet with a table in it (Table1). the table runs from Column A to Column K
I am trying to write a macro that will count all the rows in my worksheet that have data in and then re-size the table to have enough rows to incorporate all the data. This is to ensure that all the data is added to my table, even when other users have just pasted it into the worksheet.
I assume I have to count all the rows with data in by using COUNTA and then resize the table. Unfotunately, I keep getting errors on the penultimate line. I don't know if this is a syntax error or what is going wrong but I don't know how to fix it. Can anyone help?
My code is:
This is probably an easy solution but this is my first attempt at writing a macro.
I have a simple spreadsheet with a table in it (Table1). the table runs from Column A to Column K
I am trying to write a macro that will count all the rows in my worksheet that have data in and then re-size the table to have enough rows to incorporate all the data. This is to ensure that all the data is added to my table, even when other users have just pasted it into the worksheet.
I assume I have to count all the rows with data in by using COUNTA and then resize the table. Unfotunately, I keep getting errors on the penultimate line. I don't know if this is a syntax error or what is going wrong but I don't know how to fix it. Can anyone help?
My code is:
Code:
Sub AddRowsToTable()
'Find how many rows of data exist
Dim DRWS As Integer
DRWS = WorksheetFunction.CountA(ActiveSheet.Rows)
' select the table and resize it
ActiveSheet.ListObjects("Table1").Range.Select
Dim tblrng As Range
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Table1")
Set tblrng = Range("Table1[#All]").Resize("A1:K" & DRWS) 'This line is bringing up errors
tbl.Resize tblrng
End Sub
Last edited:
