Looping and Filling Info
 

I need help writing some vb script.... In Column A, every
couple of rows I have a "Identifier" then a couple of
rows of names (Column B) and I need to write code that
will Start at A2 (Or Active Cell) and Do a Select
(xlDown), Offset (-1,0) (Up one Row) and CopyDown, then
select then next identifier and do it again until the
end. For example, Cell A2 to A4 will copy down what is in
A2, A5 to A26 will copy down what is on A5, A27 to A47
will copy down what is in A27.

I have this:

Do
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.Offset(-1, 0)).Select
Selection.FillDown
Selection.End(xlDown).Select

Loop

The only problem is the offset(-1,0) is not working
right. Say it has A5:A10 Selected, instead of changing
it to A5:A9 with the Offset(-1,0) it is changing it to
A4:A10 or A5:A11, how do I get it to offset up one row
(From the bottom of the select and not from the top).

April

Looping and Filling Info
 

April

This doesn't answer your original question, but will do the Autofill for you.

''fill in blanks from cell above
Sub Fill_Blanks()
Dim myRange As Range
Set myRange = Selection
On Error GoTo stopnow
If myRange.Cells.Count = 1 Then
MsgBox "Select a range first."
Else
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Range("B1").Select
End If
stopnow:
End Sub

Gord Dibben Excel MVP

On Tue, 17 Feb 2004 13:39:06 -0800, "April" wrote:

I need help writing some vb script.... In Column A, every
couple of rows I have a "Identifier" then a couple of
rows of names (Column B) and I need to write code that
will Start at A2 (Or Active Cell) and Do a Select
(xlDown), Offset (-1,0) (Up one Row) and CopyDown, then
select then next identifier and do it again until the
end. For example, Cell A2 to A4 will copy down what is in
A2, A5 to A26 will copy down what is on A5, A27 to A47
will copy down what is in A27.

I have this:

Do
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.Offset(-1, 0)).Select
Selection.FillDown
Selection.End(xlDown).Select

Loop

The only problem is the offset(-1,0) is not working
right. Say it has A5:A10 Selected, instead of changing
it to A5:A9 with the Offset(-1,0) it is changing it to
A4:A10 or A5:A11, how do I get it to offset up one row
(From the bottom of the select and not from the top).

April

Looping and Filling Info
 

Gord,

That works but I need something that is more automated.
I have over 8,000 rows that I need to AutoFill! Can I
not get my code to do it?

April
-----Original Message-----
April

This doesn't answer your original question, but will do
the Autofill for you.

''fill in blanks from cell above
Sub Fill_Blanks()
Dim myRange As Range
Set myRange = Selection
On Error GoTo stopnow
If myRange.Cells.Count = 1 Then
MsgBox "Select a range first."
Else
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Range("B1").Select
End If
stopnow:
End Sub

Gord Dibben Excel MVP

On Tue, 17 Feb 2004 13:39:06 -0800, "April"
wrote:

I need help writing some vb script.... In Column A,
every
couple of rows I have a "Identifier" then a couple of
rows of names (Column B) and I need to write code that
will Start at A2 (Or Active Cell) and Do a Select
(xlDown), Offset (-1,0) (Up one Row) and CopyDown, then
select then next identifier and do it again until the
end. For example, Cell A2 to A4 will copy down what is
in
A2, A5 to A26 will copy down what is on A5, A27 to A47
will copy down what is in A27.

I have this:

Do
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.Offset(-1, 0)).Select
Selection.FillDown
Selection.End(xlDown).Select

Loop

The only problem is the offset(-1,0) is not working
right. Say it has A5:A10 Selected, instead of changing
it to A5:A9 with the Offset(-1,0) it is changing it to
A4:A10 or A5:A11, how do I get it to offset up one row
(From the bottom of the select and not from the top).

April

.

Looping and Filling Info
 

/Me thinks you will like:

z = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

For x = ActiveCell.Row To z
If Cells(x, 1).Value < "" Then
val1 = Cells(x, 1).Value
Else
Cells(x, 1).Value = val1
End If
Next x

- Pikus


---
Message posted from http://www.ExcelForum.com/

Looping and Filling Info
 

OH! and don't forget:

Application.ScreenUpdating = False
z = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

For x = ActiveCell.Row To z
If Cells(x, 1).Value < "" Then
val1 = Cells(x, 1).Value
Else
Cells(x, 1).Value = val1
End If
Next x
Application.ScreenUpdating = True

- Pikus


---
Message posted from http://www.ExcelForum.com/

Looping and Filling Info
 

-----Original Message-----
OH! and don't forget:

Application.ScreenUpdating = False
z = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

For x = ActiveCell.Row To z
If Cells(x, 1).Value < "" Then
val1 = Cells(x, 1).Value
Else
Cells(x, 1).Value = val1
End If
Next x
Application.ScreenUpdating = True

- Pikus


---
Message posted from http://www.ExcelForum.com/

.

Looping and Filling Info
 

Thank you, Thank you, Thank you, that worked
perfectly!!!!!



-----Original Message-----
OH! and don't forget:

Application.ScreenUpdating = False
z = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

For x = ActiveCell.Row To z
If Cells(x, 1).Value < "" Then
val1 = Cells(x, 1).Value
Else
Cells(x, 1).Value = val1
End If
Next x
Application.ScreenUpdating = True

- Pikus


---
Message posted from http://www.ExcelForum.com/

.