copying down a blank column

  • Thread starter Thread starter SoozeeC
  • Start date Start date
S

SoozeeC

I a macro which will copy the formula in the first row of a column down the
rest of the rows in that column as far as the last non-empty row in the
adjacent column, which is a variable number of rows.
Any ideas?
 
Sub CopyFormula()

With Range(Range("C2"), Range("C2").End(xlDown))
.FormulaR1C1 = Range("C1").FormulaR1C1

End With

End Sub
 
A better way is to put the formula in the macro instead

Sub copyformula()
lr = Cells(Rows.Count, "k").End(xlUp).Row
Cells(1, "L").Resize(lr).Formula = "=k1*2"
End Sub
 
You can try a few different things:
Fill Down Values:

Sub FillBlanks()
Dim rr, r As Range
Set r = Range("A2:A500")
For Each rr In r
If rr.Value = "" Then
rr.value = rr.offset(-1,0).value
End If
Next rr
End Sub


Find Last Used Cell:
Sub FindLastCell1()
Cells(Rows.Count, "A").End(xlUp).Select
End Sub

Sub FindLastCell2()
Range("A:A").Find("*", Cells(1), _
xlValues, xlWhole, xlByRows, xlPrevious).Select
End Sub


Range is A:N
FirstRow = 3
LastRow = Range("B" & Rows.Count).End(xlUp).Row
Set SortRange = Range("A" & FirstRow & ":N" & LastRow)


Hope these samples shed some light on the subject!
Ryan---
 
Hi, fairly new to VBA,

I have 5 worksheets where I have to filter and copy data from column B, C
and D to another worksheet (named: Customers) in same workbook.

Have got the code to move what data I want but need the data in each
worksheet to go into the Customers worksheet by filling in from the next
blank cell. If I copy data from worksheet A it holds but then when I copy
data from worksheet B it copies over the data...I need code please to take
the data from the each worksheet and just copy it down: My code is set as:
Sub CopySignificant()
'Copy cells of cols A,F,E,D from rows containing "Significant" in
'col D of the active worksheet (source sheet) to cols
'A,B,C,D of Sheet2 (destination sheet)
Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Customers")

Dim sRow As Long 'row index on source worksheet
Dim dRow As Long 'row index on destination worksheet
Dim sCount As Long
sCount = 0
dRow = 1

For sRow = 1 To Range("G57").End(xlUp).Row
'use pattern matching to find "Significant" anywhere in cell
If Cells(sRow, "G") Like "*GIRO*" Then
sCount = sCount + 1
dRow = dRow + 1
'copy cols A,F,E & D
DestSheet.Cells(dRow, "A") = Cells(sRow, "B")
DestSheet.Cells(dRow, "B") = Cells(sRow, "C")
DestSheet.Cells(dRow, "C") = Cells(sRow, "D")
End If
Next sRow

MsgBox sCount & " Giro Customers copied", vbInformation, "Transfer Done"

End Sub

This appears in each worksheet, can anyone help?

Thanks

Craigos
 
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.
 
Back
Top