Automatically resizing columns and rows

  • Thread starter Thread starter devm01
  • Start date Start date
D

devm01

I'd like to be able to automatically resize columns and rows to autofit
the data on loading. I regularly transfer data from text files and
access, and need to resize the columns and rows. Is there a way to do
this automatically on data load, or quickly... so that I don't have to
do it manually every time.
 
Hi,

Here are a couple of ways that allow this to be done quickly.

*Using your keyboard is still manual but can be a lot quicker than the
mouse, try:
[ctrl + home], [ctrl + shift + end], and [alt + o + c + a]
(nb: [ctrl + home] will not go to cell A1 if you have "frozen" the
window)

*This macro will autofit the columns of the used area of the active
sheet. I have included the line for autofitting the rows but this
should not be needed. If you need help installing this macro have a
look at Dave McRitchie's page,
http://www.mvps.org/dmcritchie/excel/getstarted.htm#havemacro or post
back.

Sub ResizingColumns()
Dim ImportedData As Range
Set ImportedData = ActiveSheet.UsedRange
'Range(Cells(1), ActiveCell.SpecialCells(xlLastCell))

With ImportedData
..columns.AutoFit
..rows.AutoFit 'probably not needed
End With

Set ImportedData = Nothing
End Sub


hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...
 
Back
Top