PDA

View Full Version : Help in modifying a macro



SUJU
10-26-2005, 01:07 AM
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
10-26-2005, 01:32 AM
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
10-26-2005, 03:10 AM
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
10-26-2005, 03:23 AM
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
10-26-2005, 06:22 AM
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
10-26-2005, 06:57 AM
Can you post your workbook?

Zack Barresse
10-26-2005, 11:05 AM
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
10-26-2005, 07:13 PM
Thanx Fireftr and Xld.
Phew!! that really helps
You guys are geniuses...take a bow