CODEWITHSUNDEEP
vbaexcel
MTT
MTT

Reputation: 379

Application-defined or object-defined error in setting column width

I am trying to run this code snippet to set the column width from the firstcolumn to the last one to 4.

I am getting the error saying: Application-defined or object-defined error

Dim firstRow, firstCol As Integer
Dim lastRow, lastCol As Integer
Dim row, column As Integer

firstRow = start.row
firstCol = start.column
lastRow = iMax
lastCol = jMax

'Set column width to 4
Sheets("Tree").Columns(Cells(firstRow, firstCol).EntireColumn, Cells(firstRow, lastCol - 1).EntireColumn).ColumnWidth = 4

Where jMax and iMax are 2 integers. Any help? Thanks!

Upvotes: 0

Views: 267

Answers (1)

BigBen
BigBen

Reputation: 50162

Worksheet.Columns takes column letter(s) or index as its argument.

So Sheets("Tree").Columns("A:B").ColumnWidth = 4, or Sheets("Tree").Columns(2).ColumnWidth = 4 are examples of the correct syntax. But you have Range arguments here.

If you change Columns to Range, and all of your variables have the expected values, the last line should work.

A couple recommendations:

  • Change the Integers to Long - Excel has more rows than Integer can handle, and there's no real benefit to using Integer.
  • With variable declaration, you need to explicitly state the type for each variable and not just at the end - Dim firstRow as Long, firstCol as Long. With Dim firstRow, firstCol as Long, firstRow is actually a Variant
  • Since a Range already has Row and Column properties, it's preferable to avoid using those terms as variables.

Upvotes: 1

Related Questions