Sizing columns and rows in a workbook

[CAM]

Hello,

I have a workbook that has one worksheet. It's a download and I am always
trying to size the column and rows. I want to have a way I can use VBA
coding to size the columns and rows based on the size of the columns and
rows in a consistent manner. Can anyone help! Thank you advance.

Cheers

 

[marcus]

Hi Cam

Here is a start you should be able to change to suit your
requirements. It assumes your datas length is determined by coumn As
row length. It assumes your download goes from Columns A to N. It
also assumes you want the columns autofit. Lot of assumptions.

Good luck

Marcus

Sub ChangeHeight()
Dim lw As Integer

lw = Range("A" & Rows.Count).End(xlUp).Row

For i = lw To 2 Step -1
Range("A" & i).EntireRow.AutoFit
Range("A:N").EntireColumn.AutoFit
Next i

End Sub

 

[Gary Keramidas]

i used the first worksheet in this code because the name may change with your
download. if it's not the first sheet, change the 1 to another number or if the
sheet name is always the same, use that instead.

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Dim lastcol As Long
Set ws = Worksheets(1)
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
lastcol = ws.Cells(1, Columns.Count).End(xlToLeft).Column

With ws.Range(ws.Cells(1, "A").Address & ":" & ws.Cells(lastrow, _
lastcol).Address)
.Columns.AutoFit
.Rows.AutoFit
End With

End Sub