Need macro for copying random lines of data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FeFi - 13 Mar 2008 18:11 GMT

Brief description of problem - Col A contains data in random cells down the
sheet, i.e., A1, A23, A25, A86, etc., each of which is common to data found
in rows in adjoining columns. In this example I need to copy A1 to A2 thru
A22; copy A23 to A24; A25 to A26 thru A85; etc. If I copy the cell, use
"End" "Down" to find the next cell with data, use the "Up" arrow key to go to
the blank cell above, paste the data, then use "End" Down" to go to the next
cell to be copied, everything if fine. I created a macro doing this (using
relative reference) but it doesn't work correctly. I'm sure it has to do
with the cell references but I don't know how to edit it. This procedure is
required monthly on a very large spreadsheet (data in Col A is never in the
same rows) and I want to be able to loop this in order to save time. The
macro I created is shown below - can anyone fix it to do what I want?
Sub Copy_Code_to_Blanks()
'
' Copy_Code_to_Blanks Macro
' Macro recorded 3/13/2008 by XXXXXXXXX
'

'
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A22").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
End Sub

Reply to this Message

Tom Hutchins - 13 Mar 2008 18:56 GMT

Here is a macro which does what you want. It was written by Dave Peterson; I
got it from Debra Dalgleish's wondersul site:
http://www.contextures.com/xlDataEntry02.html

Sub FillColBlanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim col As Long
Set wks = ActiveSheet
With wks
col = ActiveCell.Column
Set rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(2, col), .Cells(LastRow, col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "=R[-1]C"
End If
'replace formulas with values
With .Cells(1, col).EntireColumn
.Value = .Value
End With
End With
End Sub

Hope this helps,

Hutch

> Brief description of problem - Col A contains data in random cells down the
> sheet, i.e., A1, A23, A25, A86, etc., each of which is common to data found
[quoted text clipped - 21 lines]
> Selection.End(xlDown).Select
> End Sub

Reply to this Message

Tom Hutchins - 13 Mar 2008 19:18 GMT

I should mention one more thing. Click any cell in the column (A?) where you
want the values copied down before running the macro. It looks at the active
sheet and active column.

Hutch

> Here is a macro which does what you want. It was written by Dave Peterson; I
> got it from Debra Dalgleish's wondersul site:
[quoted text clipped - 59 lines]
> > Selection.End(xlDown).Select
> > End Sub

Reply to this Message

FeFi - 13 Mar 2008 19:47 GMT

This did the job - should save me a lots of time. Thanks so much!

Reply to this Message