Help in modifying a macro

[SUJU]

Hi
I have the following macro that adds rows between existing row values. How do i change it so that it repeats the value of the cell above the inserted row.
Eg, If A6=Nails; A7=Screws, A8= Bolts and if i insert 5 rows between each of them, i want rows A7-A11 to repeat the name Nails and A12-A17 = Screws ...and so on.


Sub xxxx()
Dim myRange As Range
Dim c As Range

Set myRange = Range("A6:A65536")
x = InputBox("Enter Number ") '3

i = Application.WorksheetFunction.CountA(myRange)
For Each c In myRange
If c.Value <> "" Then
Range(c.Offset(1, 0), c.Offset(x, 1)).EntireRow.Insert
End If
Next c
End Sub


Any help will be appreciated
Thanks

[Bob Phillips]

Sub xxxx()
Dim myRange As Range
Dim c As Range
Dim iLastRow As Long
Dim i As Long
Dim x

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
x = InputBox("Enter Number") '3

For i = iLastRow To 6 Step -1
If Cells(i, "A").Value <> "" Then
Rows(i + 1).Resize(x).Insert
Cells(i, "A").AutoFill Cells(i, "A").Resize(x + 1)
End If
Next i
End Sub

[SUJU]

Thanx XLD.
The code runs faster than before. One small question:
When i filter data down into the adjacent colmns, i notice that they only filter down to the end of the first group..thats is from A6-A11 (Nails)....and not not all the way down the colmn. Why is that. The old code did not affect the corresponding colmns.

Thanx again

[Bob Phillips]

Thanx XLD.
The code runs faster than before. One small question:
When i filter data down into the adjacent colmns, i notice that they only filter down to the end of the first group..thats is from A6-A11 (Nails)....and not not all the way down the colmn. Why is that. The old code did not affect the corresponding colmns.

I am not sure that I understand what you mean.

[SUJU]

Adjacent to each value in Col A, dates and qty is filtered into the workbook from an external source. With the old code, the dates and qties were filtering in without a problem. However, with your code, the dates and qties only filter up the end of the group of names (Nails, in this case). I cant seem to understand why the change in your code would interfere with the adjacent columns.

HTH

[Bob Phillips]

Can you post your workbook?

[Zack Barresse]

It's because of this line ...

Cells(i, "A").AutoFill Cells(i, "A").Resize(x + 1)

It does not take into account any other columns except that of A. As an example, pick this apart ...

Option Explicit

Sub AdjustColsToo()
Dim c As Range, r As Range
Dim i As Long, x As Long
Set r = Cells(i, "A")
Set c = r.Resize(x + 1, 2)
r.AutoFill c
End Sub

[SUJU]

Thanx Fireftr and Xld.
Phew!! that really helps
You guys are geniuses...take a bow