Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need macro for copying random lines of data
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need macro for copying random lines of data
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 "FeFi" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need macro for copying random lines of data
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 "Tom Hutchins" wrote: 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 "FeFi" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need macro for copying random lines of data
This did the job - should save me a lots of time. Thanks so much!
"Tom Hutchins" wrote: 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 "FeFi" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying data across lines | Excel Discussion (Misc queries) | |||
Inserting Lines or Copying lines with formulas but without data | Excel Discussion (Misc queries) | |||
can you assign random numbers to many different lines of row? | Excel Worksheet Functions | |||
Getting rid of random white lines in Excel | Excel Discussion (Misc queries) | |||
Non-random numbers generated by excel's data analysis random gener | Excel Worksheet Functions |