Insert 8 Rows

[Connie Martin]

How to I adapt this formula to insert 8 blanks rows? Right now it inserts
only one, and I can't read this stuff! Thank you. Connie

Sub InsertRow_At_Change()
Dim i As Long
Dim colno As String
'Dim colno as Long
colno = InputBox("Enter a Column Letter")
'colno = InputBox)"Enter a Column Number")
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1
If Cells(i - 1, colno) <> Cells(i, colno) Then _
Cells(i, colno).Resize(1, 1).EntireRow.Insert
Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

 

[Don Guillett]

From the vba HELP index for RESIZE

Resize Property
See AlsoApplies ToExampleSpecifics
Resizes the specified range. Returns a Range object that represents the
resized range.

expression.Resize(RowSize, ColumnSize)

expression Required. An expression that returns a Range object to be
resized.

RowSize Optional Variant. The number of rows in the new range. If this
argument is omitted, the number of rows in the range remains the same.

ColumnSize Optional Variant. The number of columns in the new range. If
this argument is omitted, the number of columns in the range remains the
same.

Example
This example resizes the selection on Sheet1 to extend it by one row and one
column.

Worksheets("Sheet1").Activate
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 1, numColumns + 1).Select
This example assumes that you have a table on Sheet1 that has a header
row. The example selects the table, without selecting the header row. The
active cell must be somewhere in the table before you run the example.

Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Select
-- Don GuillettMicrosoft MVP ExcelSalesAid
(e-mail address removed)"Connie Martin"

 

[Connie Martin]

I'm sorry, Don, but you lost me!

From the vba HELP index for RESIZE

Resize Property
See AlsoApplies ToExampleSpecifics
Resizes the specified range. Returns a Range object that represents the
resized range.

expression.Resize(RowSize, ColumnSize)

expression Required. An expression that returns a Range object to be
resized.

RowSize Optional Variant. The number of rows in the new range. If this
argument is omitted, the number of rows in the range remains the same.

ColumnSize Optional Variant. The number of columns in the new range. If
this argument is omitted, the number of columns in the range remains the
same.

Example
This example resizes the selection on Sheet1 to extend it by one row and one
column.

Worksheets("Sheet1").Activate
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
Selection.Resize(numRows + 1, numColumns + 1).Select
This example assumes that you have a table on Sheet1 that has a header
row. The example selects the table, without selecting the header row. The
active cell must be somewhere in the table before you run the example.

Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Select
-- Don GuillettMicrosoft MVP ExcelSalesAid
(e-mail address removed)"Connie Martin"

 

[John Bundy]

I think he posted the wrong thing, the simplest method would just be to copy
the insert line (though some would shoot me for not being more confusing).

Sub InsertRow_At_Change()
Dim i As Long
Dim colno As String
'Dim colno as Long
colno = InputBox("Enter a Column Letter")
'colno = InputBox)"Enter a Column Number")
With Application
.Calculation = xlManual
.ScreenUpdating = False
End With
For i = Cells(Rows.Count, colno).End(xlUp).Row To 2 Step -1
If Cells(i - 1, colno) <> Cells(i, colno) Then _
Cells(i, colno).Resize(1, 1).EntireRow.Insert
Cells(i, colno).Resize(1, 1).EntireRow.Insert
Cells(i, colno).Resize(1, 1).EntireRow.Insert
Cells(i, colno).Resize(1, 1).EntireRow.Insert
Cells(i, colno).Resize(1, 1).EntireRow.Insert
Cells(i, colno).Resize(1, 1).EntireRow.Insert
Cells(i, colno).Resize(1, 1).EntireRow.Insert
Cells(i, colno).Resize(1, 1).EntireRow.Insert

Next i
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

 

[Don Guillett]

change
Cells(i, colno).Resize(1, 1).EntireRow.Insert
to
Cells(i, colno).Resize(8, 1).EntireRow.Insert

 

[Connie Martin]

I played around with my original formula, and found that all you have to do
is change the part that says "Resize(1, 1)" to "Resize(8, 1)". That's all I
was looking for, and I guess I should've dug deeper before posting. Thank
you anyways for replying. Your formula works, as well. Thank you. Connie

 

[Don Guillett]

That's exactly what I told you to do in my first post

 

[Connie Martin]

Sorry, Don, I got lost with all the info.

That's exactly what I told you to do in my first post
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Connie Martin]

Bingo! That does it! Thank you. Connie

change
Cells(i, colno).Resize(1, 1).EntireRow.Insert
to
Cells(i, colno).Resize(8, 1).EntireRow.Insert


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)