Macro doesn't use "end-down" to count rows to paste

  • Thread starter Thread starter Paul Haydock
  • Start date Start date
P

Paul Haydock

Hi
I am trying to record a macro - (and I don't know VB) -
to copy and paste cells down into blank cells - but the
number of rows/cells varies.
e.g. I want a macro to copy the letter down to fill the
blank cells:
A 1
2
3
B 1
2
C 1
2
3
4
D 1
My macro should copy the letter (will be "A" at first)
and fill the empty cells beneath using the end-down key.
Once recorded, running the macro should do the same to B
and C, stopping at the appropriate number of cells. BUT
the macro always pasted 2 rows, not 1, then 3, etc as
required. Even though I have clicked the RELATIVE button,
it still records like this: - I feel the "A1:A3" is
causing the prob... I think that should be flexible.
Sub Macro10()
'
' Macro10 Macro
' Macro recorded 21/08/2003 by Paul Haydock
'
' Keyboard Shortcut: Ctrl+n
'
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A3").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
End Sub

Regs
PAUL HAYDOCK
 
try

Sub copytoblank()
x = Cells(65536, 1).End(xlUp).Row
For Each c In Range(Cells(1, "A"), Cells(x, "A"))
If c.Offset(1) = "" Then c.Offset(1) = c
Next
End Sub
 
Paul

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 XL2002
 
Back
Top