How to set a range with cells?

Question

I would like to set a range to fit width columns, but I would like to set using numbers, no letters, something like this:

myWorksheet.Range(Cells(1, 1), Cells(1, 13)).Columns.AutoFit

But I get an error 400.

If I use

myWorksheet.Range("A1:M1").Columns.AutoFit

It works perfecly.

How I could to use cells to set the range and to do the AutoFit? Because I am using variables to set the range and I can't use letters, I have to use the numbers.

Thanks.

FaneDuru · Accepted Answer · 2020-09-01T11:42:11.687

1

Try, please:

myWorksheet.Range(myWorksheet.Cells(1, 1), myWorksheet.Cells(1, 13)).EntireColumn.AutoFit

If your active worksheet is not myWorksheet, the ranges will belong to the active sheet. Then, EntireColumn ca be Autofit, not a cells range...

edited Sep 01 '20 at 11:42

answered Sep 01 '20 at 11:40

FaneDuru

38,298
4
19
27

It is better to use the `.Resize()` modifier. Like `myWorksheet.Range("A1").Resize(1,13).EntireColumn.AutoFit` – John Alexiou Sep 01 '20 at 17:22

score 1 · Answer 2 · answered Sep 01 '20 at 11:48

1

You could use:

Sub test()
  
    With ThisWorkbook.Worksheets("myWorksheet")
    
        .Range(.Cells(1, 1), .Cells(1, 13)).EntireColumn.AutoFit
        
    End With

End Sub

answered Sep 01 '20 at 11:48

Error 1004

7,877
3
23
46

score 0 · Answer 3 · answered Sep 01 '20 at 11:47

0

you have to define cells in range..

you can try this

Dim Rg1 As Range
Dim Rg2 As Range

    Set Rg1 = Cells(1, 1)
    Set Rg2 = Cells(1, 13)

    Range(Rg1, Rg2).EntireColumn.AutoFit

answered Sep 01 '20 at 11:47

Khaled Sayed

70
6

How to set a range with cells?

3 Answers3