Excel vba autofit rows

How to AutoFit rows using macros in Excel

In this guide, we’re going to show you how to autofit rows using macros in Excel.

Autofit Method

You can use the AutoFit feature by calling the method with same name: AutoFit. The AutoFit method is defined under Range object which represents a cell, row, column, or selection of cells. To AutoFit rows using macros, you need to express the rows you want to autofit as the range object. Below are some examples.

Worksheets(«Sheet1»).Rows(«1:10»).AutoFit	AutoFits rows through 1 st to 10 th in Sheet1.
Worksheets(«Sheet1»).Range(«A5:A20»).Rows.AutoFit	AutoFits rows through 5 th to 20 th in Sheet1.
Worksheets(«Sheet1»).Rows(5).AutoFit	AutoFits the row 5 in Sheet1.
Worksheets(«Sheet1»).Cells.EntireRow.AutoFit	AutoFits all the row in Sheet1.

Each command above ends with the AutoFit method. Note that the Rows method can return a range object you need to run the AutoFit method.

How to create a macro autofit rows using macros

Let’s review the basics of creating macros. To start, you need to open the VBA (Visual Basic for Applications) window and add a module. A module is where you write code.

1. Press Alt + F11 to open the VBA window.
2. In the VBA window, click Insert on the toolbar.
3. Click the Module option.
4. Create a subroutine by typing in Sub MyAutoFit and pressing Enter (MyAutoFit is just a placeholder, feel free to change it).
5. Enter your code between Sub MyAutoFit and End Sub (End Sub will be created automatically upon Enter key.).
   

Remember to save your workbook as an XSLM (Macro enabled workbook) after entering your code. Otherwise, your code will not be saved.

Источник

VBA Set Column Width or Row Height

In this Article

This tutorial will demonstrate how to set row height and column widths using VBA.

Excel Row heights and Columns widths can be changed in VBA by setting the .RowHeight and .ColumnWidth properties.

Set Column Width with VBA

Macro to set the column width of Columns A to E:

Set Row Height with VBA

Macro to set the row height of Row 1:

Autofit Column Width

Excel offers the ability to “Autofit” column widths. This feature adjusts the column width so that the column(s) is wide enough to fit all text found in that column.

To Autofit column widths in VBA:

We wrote more about this in another article on how to Autofit a Column from VBA, including how to Autofit all used columns.

Autofit Row Height

You can also autofit row heights using a similar method:

Set Cell Width

You can also adjust column widths by referencing a cell:

Set Cell Height

Or adjust row heights by referencing a cell:

Obtain Column Width

To obtain the column width of a column:

Note: This will return Null if all columns in the range do not have the same width.

Obtain Row Height

Similarly, you can obtain the row height:

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

Источник

VBA Excel. Размер ячейки (высота строки, ширина столбца)

Изменение размера ячейки в VBA Excel. Высота строки, ширина столбца, автоподбор ширины ячейки. Свойства RowHeight и ColumnWidth объекта Range.

Размер ячейки

Размер ячейки по высоте и ширине определяется высотой строки и шириной столбца, на пересечении которых она находится. Если, в вашем случае, нежелательно изменять размеры всей строки или всего столбца, используйте объединенные ячейки нужной величины.

Обратите внимание, что высота строки задается в пунктах, а ширина столбца в символах, поэтому их числовые значения не соответствуют друг другу по фактическому размеру.

Высота строки и ширина столбца в Excel

Программно, без дополнительных макросов, можно изменять высоту строки только в пунктах, а ширину столбца только в символах.

На сайте поддержки офисных приложений Microsoft так написано об этих величинах:

• высота строки может принимать значение от 0 до 409 пунктов, причем 1 пункт приблизительно равен 1/72 дюйма или 0,035 см;
• ширина столбца может принимать значение от 0 до 255, причем это значение соответствует количеству символов, которые могут быть отображены в ячейке.

Смотрите, как сделать все ячейки рабочего листа квадратными.

Высота строки

Для изменения высоты строки используйте свойство RowHeight объекта Range. И не важно, будет объект Range представлять из себя выделенный произвольный диапазон, отдельную ячейку, целую строку или целый столбец — высота всех строк, пересекающихся с объектом Range будет изменена после присвоения свойству RowHeight этого объекта нового значения.

Примеры изменения высоты строк:

Пример 1
Изменение высоты отдельной ячейки:

в результате, строка, в которой находится активная ячейка, приобретает высоту, равную 10 пунктам.

Пример 2
Изменение высоты строки:

в результате, третья строка рабочего листа приобретает высоту, равную 30 пунктам.

Пример 3
Изменение высоты ячеек заданного диапазона:

в результате, каждой из первых шести строк рабочего листа будет задана высота, равная 20 пунктам.

Пример 4
Изменение высоты ячеек целого столбца:

в результате, всем строкам рабочего листа будет назначена высота, равная 15 пунктам.

Ширина столбца

Для изменения ширины столбца используйте свойство ColumnWidth объекта Range. Как и в случае с высотой строки, не важно, будет объект Range представлять из себя выделенный произвольный диапазон, отдельную ячейку, целую строку или целый столбец — ширина всех столбцов, пересекающихся с объектом Range будет изменена после присвоения свойству ColumnWidth этого объекта нового значения.

Примеры изменения ширины столбцов:

Пример 1
Изменение ширины отдельной ячейки:

в результате, столбец, в котором находится активная ячейка, приобретает ширину, равную 15 символам.

Пример 2
Изменение ширины столбца:

в результате, третий столбец рабочего листа (столбец «C») приобретает ширину, равную 50 символам.

Пример 3
Изменение ширины ячеек заданного диапазона:

в результате, каждому из первых четырех столбцов рабочего листа будет задана ширина, равная 25 символам.

Пример 4
Изменение ширины ячеек целой строки:

в результате, всем столбцам рабочего листа будет назначена ширина, равная 35 символам.

Автоподбор ширины

Для автоподбора ширины ячейки в соответствие с размером ее содержимого используйте следующий код:

Источник

VBA AutoFit (Rows, Column, or the Entire Worksheet)

Key Points

• In VBA, you can use the AutoFit method to auto-fit rows, columns, and even an entire worksheet.
• You need to specify the range, and then you can use the AutoFit method.

AutoFit a Column

Let’s say you want to autofit column A, the code would be something like below:

In the above line of code, you have used the EntireColumn property to refer to the entire column of cell A1.

As you are within a worksheet so you can also use the columns property and write a code like the below.

AutoFit a Row

In the same way, you can write code to autofit a row. Let’s say you want to autofit row 5, the code would be:

And if you want to use the row property, then you can use the code like the following.

AutoFit UsedRange (Rows and Columns)

Now let’s say, you only want to autofit those columns and rows where you have data. In VBA, there is a property called used range that you can use. So the code would be.

And if you want to use a specific worksheet then the code would be.

AutoFit Entire Worksheet

And if you want to refer to all the columns and rows of the worksheet then you can use the “CELLS” property. Here’s the code.

Or you can also use VBA’s WITH statement to write a code like the below.

Источник

Excel VBA Ranges and Cells

In this Article

Ranges and Cells in VBA

Excel spreadsheets store data in Cells. Cells are arranged into Rows and Columns. Each cell can be identified by the intersection point of it’s row and column (Exs. B3 or R3C2).

An Excel Range refers to one or more cells (ex. A3:B4)

Cell Address

A1 Notation

In A1 notation, a cell is referred to by it’s column letter (from A to XFD) followed by it’s row number(from 1 to 1,048,576). This is called a cell address.

In VBA you can refer to any cell using the Range Object.

R1C1 Notation

In R1C1 Notation a cell is referred by R followed by Row Number then letter ‘C’ followed by the Column Number. eg B4 in R1C1 notation will be referred by R4C2. In VBA you use the Cells Object to use R1C1 notation:

Range of Cells

A1 Notation

To refer to a more than one cell use a “:” between the starting cell address and last cell address. The following will refer to all the cells from A1 to D10:

R1C1 Notation

To refer to a more than one cell use a “,” between the starting cell address and last cell address. The following will refer to all the cells from A1 to D10:

Writing to Cells

To write values to a cell or contiguous group of cells, simple refer to the range, put an = sign and then write the value to be stored:

Reading from Cells

To read values from cells, simple refer to the variable to store the values, put an = sign and then refer to the range to be read:

Note: To store values from a range of cells, you need to use an Array instead of a simple variable.

Non Contiguous Cells

To refer to non contiguous cells use a comma between the cell addresses:

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Intersection of Cells

To refer to non contiguous cells use a space between the cell addresses:

Offset from a Cell or Range

Using the Offset function, you can move the reference from a given Range (cell or group of cells) by the specified number_of_rows, and number_of_columns.

Offset Syntax

Offset from a cell

Offset from a Range

Setting Reference to a Range

To assign a range to a range variable: declare a variable of type Range then use the Set command to set it to a range. Please note that you must use the SET command as RANGE is an object:

Resize a Range

Resize method of Range object changes the dimension of the reference range:

Top-left cell of the Resized range is same as the top-left cell of the original range

Resize Syntax

OFFSET vs Resize

Offset does not change the dimensions of the range but moves it by the specified number of rows and columns. Resize does not change the position of the original range but changes the dimensions to the specified number of rows and columns.

All Cells in Sheet

The Cells object refers to all the cells in the sheet (1048576 rows and 16384 columns).

UsedRange

UsedRange property gives you the rectangular range from the top-left cell used cell to the right-bottom used cell of the active sheet.

CurrentRegion

CurrentRegion property gives you the contiguous rectangular range from the top-left cell to the right-bottom used cell containing the referenced cell/range.

Range Properties

You can get Address, row/column number of a cell, and number of rows/columns in a range as given below:

Last Cell in Sheet

You can use Rows.Count and Columns.Count properties with Cells object to get the last cell on the sheet:

Last Used Row Number in a Column

END property takes you the last cell in the range, and End(xlUp) takes you up to the first used cell from that cell.

Last Used Column Number in a Row

END property takes you the last cell in the range, and End(xlToLeft) takes you left to the first used cell from that cell.

You can also use xlDown and xlToRight properties to navigate to the first bottom or right used cells of the current cell.

Cell Properties

Common Properties

Here is code to display commonly used Cell Properties

Cell Font

Cell.Font object contains properties of the Cell Font:

Copy and Paste

Paste All

Ranges/Cells can be copied and pasted from one location to another. The following code copies all the properties of source range to destination range (equivalent to CTRL-C and CTRL-V)

Paste Special

Selected properties of the source range can be copied to the destination by using PASTESPECIAL option:

Here are the possible options for the Paste option:

AutoFit Contents

Size of rows and columns can be changed to fit the contents using AutoFit:

More Range Examples

It is recommended that you use Macro Recorder while performing the required action through the GUI. It will help you understand the various options available and how to use them.

For Each

It is easy to loop through a range using For Each construct as show below:

At each iteration of the loop one cell in the range is assigned to the variable cell and statements in the For loop are executed for that cell. Loop exits when all the cells are processed.

Sort is a method of Range object. You can sort a range by specifying options for sorting to Range.Sort. The code below will sort the columns A:C based on key in cell C2. Sort Order can be xlAscending or xlDescending. Header:= xlYes should be used if first row is the header row.

Find is also a method of Range Object. It find the first cell having content matching the search criteria and returns the cell as a Range object. It return Nothing if there is no match.

Use FindNext method (or FindPrevious) to find next(previous) occurrence.

Following code will change the font to “Arial Black” for all cells in the range which start with “John”:

Following code will replace all occurrences of “To Test” to “Passed” in the range specified:

It is important to note that you must specify a range to use FindNext. Also you must provide a stopping condition otherwise the loop will execute forever. Normally address of the first cell which is found is stored in a variable and loop is stopped when you reach that cell again. You must also check for the case when nothing is found to stop the loop.

Range Address

Use Range.Address to get the address in A1 Style

Use xlReferenceStyle (default is xlA1) to get addres in R1C1 style

This is useful when you deal with ranges stored in variables and want to process for certain addresses only.

Range to Array

It is faster and easier to transfer a range to an array and then process the values. You should declare the array as Variant to avoid calculating the size required to populate the range in the array. Array’s dimensions are set to match number of values in the range.

Array to Range

After processing you can write the Array back to a Range. To write the Array in the example above to a Range you must specify a Range whose size matches the number of elements in the Array.

Use the code below to write the Array to the range D1:D5:

Please note that you must Transpose the Array if you write it to a row.

Sum Range

You can use many functions available in Excel in your VBA code by specifying Application.WorkSheetFunction. before the Function Name as in the example above.

Count Range

Written by: Vinamra Chandra

VBA Code Examples Add-in

Easily access all of the code examples found on our site.

Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.

Источник