RunTime Error - Method 'Range' of object '_Global' failed

Question

Hi I am trying to write a macro to adjust the number of columns in a certain table. Sometimes the table is like from rows A to Z, and I just want it rows A to L. Whenever I run my macro however, it gives the error RunTime Error - Method 'Range' of object '_Global' failed.

Sub ResizeTable()

Columns("L:L").Select
ActiveSheet.ListObjects("Table_macroconnection").Resize Range("$A$1:$L$" & numRows)
Columns("L:L").Select

End Sub

How do I fix this? Thanks. ==========EDIT==========

Fixed! Changed numRows to ActiveSheet.UsedRange.Rows.Count.

Duh. (In a previous program I wrote I had set numRows to ActiveSheet.UsedRange.Rows.Count, and mistakenly thought numRows was some built-in excel value.

this question is of no value to any future reader feel free to delete it or provide your own answer instead of editing the actual question block cause that's not how Stack Overflow works. — user2140173
See this for the best way to find the last row in a column/range. — David Zemens
Thanks @mehow, David Zemens, and tannman357. I currently do not have enough rep to answer my own question or to vote up an answer. — johnsimer

Tanner Tanner · Accepted Answer · 2014-06-30T11:46:29

Just so that this question has an answer and shows up as answered (I think his rep points are keeping him from answering):

Change your numRows to ActiveSheet.UsedRange.Rows.Count and it will work.

RunTime Error - Method 'Range' of object '_Global' failed

1 Answers