CODEWITHSUNDEEP
vbaexcel
johnsimer
johnsimer

Reputation: 350

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

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.

Upvotes: 0

Views: 845

Answers (1)

Tanner
Tanner

Reputation: 578

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.

Upvotes: 1

Related Questions