How to insert blank rows of a specific height for selection?

Question

I have started an Excel macro to insert a blank row after selecting, but I want the blank rowheight = 6. I'm new to VBA, but am eager to learn.

This is for a project at work.

Dim Rng As Range
Dim WorkRng As Range
Dim FirstRow As Integer, xRows As Integer, xCols As Integer
On Error Resume Next
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", WorkRng.Address, Type:=8)
FirstRow = WorkRng.Row
xRows = WorkRng.Rows.count
xCols = WorkRng.Columns.count
Application.ScreenUpdating = False
WorkRng.Cells(xRows, 1).Resize(1, xCols).Select

Do Until Selection.Row = FirstRow
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Offset(-1, 0).Select

Loop

Application.ScreenUpdating = True
End Sub

Start on the right foot. Stop using On Error Resume Next until you know how. http://www.cpearson.com/Excel/ErrorHandling.htm — niton, Aug 17 '17 at 18:03

score 1 · Answer 1 · answered Aug 17 '17 at 17:54

1

You want the .RowHeight() property:

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Offset(1,0).RowHeight = 6
Selection.Offset(-1, 0).Select

...But please note it's best to Avoid using .Select/.Activate

answered Aug 17 '17 at 17:54

BruceWayne

22,923
15
65
110

Thank you! I'm also having trouble adding spacer columns after selecting a group of columns, and I would also like them to be a specific width. – Wendi Aug 30 '17 at 20:45

score 0 · Accepted Answer · answered Aug 30 '17 at 22:40

To insert rows, use code like rows(3).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove and to change column width use the .ColumnWidth property. The code below inserts the spacer columns you mention.

Option Explicit
Sub insertSpacerColumns()
Dim r As Range, i As Integer, startCol As Integer, numSpacers As Integer
Set r = Application.InputBox("Select columns", , , Type:=8)
i = r(, 1).Column + 1
numSpacers = r(, r.columns.Count).Column - i + 1
Do
  columns(i).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  columns(i).ColumnWidth = 1
  i = i + 2
  numSpacers = numSpacers - 1
Loop Until numSpacers = 0
End Sub

How to insert blank rows of a specific height for selection?

2 Answers2