View Single Post
  #11   Report Post  
Posted to microsoft.public.excel.misc
colwyn colwyn is offline
external usenet poster
 
Posts: 29
Default How do I automatically copy/paste ?

Thanks Gord Bet you'll be glad to hear that !!
Thanks for all.
Colwyn.



On Sep 11, 4:04*pm, Gord Dibben <gorddibbATshawDOTca wrote:
Run this macro

Sub Fill_Blanks()
'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
* * 'or
* * 'col = .range("b1").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.NumberFormat = "General"
* * * * * * rng.FormulaR1C1 = "=R[-1]C"
* * * * End If

* * 'replace formulas with values
* * * * With .Cells(1, Col).EntireColumn
* * * * * * .Value = .Value
* * * * End With

* * End With

End Sub

Gord

On Thu, 11 Sep 2008 01:47:39 -0700 (PDT), colwyn

wrote:
Gord, very many thanks for your help
Your code only works when there are numbers in all cells.
The column I use it on is column A. Now if the first block of data is
6 rows deep, cell A1 contains 1. The next number to appear will be 2
in cell A7. Cells A2:A6 are blank.
Is there a workaround for this in your code ??
Thanks again.
Colwyn.


On Sep 11, 1:27*am, Gord Dibben <gorddibbATshawDOTca wrote:
Sub InsertRow_At_Change()
'Sandy Mann July 1st, 2007
* * Dim LastRow As Long
* * Dim X As Long
* * LastRow = Cells(Rows.Count, 1).End(xlUp).Row
* * Application.ScreenUpdating = False


* * For X = LastRow To 3 Step -1
* * * * If Cells(X, 1).Value < Cells(X - 1, 1).Value Then
* * * * * * If Cells(X, 1).Value < "" Then
* * * * * * * * If Cells(X - 1, 1).Value < "" Then
* * * * * * * * * * Cells(X, 1).entirerow.Insert Shift:=xlDown
* * * * * * * * End If
* * * * * * End If
* * * * End If
* * Next X
* * Application.ScreenUpdating = True
End Sub


Assumes you have like values in Column A which designate the break in
blocks,


Gord


On Wed, 10 Sep 2008 15:17:10 -0700 (PDT), colwyn


wrote:


The spreadsheet contains a series of blocks of data and there are no
breaks between rows. I want to put a blank row between each block of
data. Any suggestions as to how I might go about this ??
It would be really helpful if I could achieve this end.
Thanks.
Colwyn.