CODEWITHSUNDEEP
excelvba
joehua
joehua

Reputation: 735

Sheets.Columns to hide columns

I can hide columns by using the following code:

ThisWorkbook.Sheets(1).Columns("G:K").EntireColumn.Hidden = True

Now, I have the last column in number, not letter.

last_column= Sheets(1).UsedRange.SpecialCells(xlCellTypeLastCell).Column

I guess I could convert the last_column to the corresponding letter and use the first code to hide columns. Is there a more direct way of doing it without resorting to converting number to letter?

I have tried different variations to replace "G:K" with numbers with no avail.

Upvotes: 0

Views: 74

Answers (3)

L42
L42

Reputation: 19727

Or you can try Resize.

With ThisWorkbook.Sheets(1)
  .Columns("G").Resize(, last_column - 6).Hidden = True
End With

Upvotes: 0

Mikku
Mikku

Reputation: 6654

Try: 

With ThisWorkbook.Sheets(1)
    .Range(.Columns("G"), .Columns(last_column)).EntireColumn.Hidden = True
End With

Upvotes: 1

Michael
Michael

Reputation: 4828

Determine intStartCol and intEndCol as the numbers of your first and last columns, then you can hide by:

With Sheets(1)
    .Range(.Columns(intStartCol),.Columns(intEndCol)).Hidden = True
end with

Upvotes: 1

Related Questions