Find the End of Data File in VBA

[dkauf]

I have a Macro I've developed that takes a raw database export file and runs some formulas/cleans up the data so that it can be imported seamlessly into a different database. KEY - the raw data file can contain any number of rows (values).

To work around the unknown number of rows, I wrote my formulas in VBA to check the first column for a non-blank and then copied that same formula down to Row 5000 (as it is unlikely there would be that many rows with data in the raw file). NOTE - the formula returned "" if the contents in the first column was blank.

This works for the most part, but the database that is then receiving this file is interpreting the Blank "" cells as actually containing values and is rejecting the data input. As a very novice VBA writer, I am hoping to find a way to write a formula in my macro that works like the double-click on the bottom right of a cell you wish to copy down to all adjacent cells (i.e. I would modify my existing VBA code to only copy down until the last row with "real" data as opposed to copying all the way down to Row 5000.

A very basic example of my current work is below:

Uploaded with ImageShack.us

 

[Asator]

If your formula column is Col D:

With ActiveSheet
.Range(.Cells(1, 4), .Cells(.UsedRange.Rows.Count, 4)).FormulaR1C1 = "=YOURFORMULA IN R1C1 FORMAT"
End With

 

[dkauf]

If your formula column is Col D:

With ActiveSheet
.Range(.Cells(1, 4), .Cells(.UsedRange.Rows.Count, 4)).FormulaR1C1 = "=YOURFORMULA IN R1C1 FORMAT"
End With

Thank you. How would I modify the following code to follow the suggested syntax above?

    Range("X2:AA2").Select
    Selection.AutoFill Destination:=Range("X2:AA5000"), Type:=xlFillDefault

 

[lenze]

This should do what you want

Sub AddFormula()
Dim LR As Long
LR = Cells(Rows.Count, "B").End(xlUp).Row
Cells(2, "D").Resize(LR - 1, 1).Formula = "=YourFormula" 'For D2
End Sub

lenze

 

[dkauf]

This should do what you want

Sub AddFormula()
Dim LR As Long
LR = Cells(Rows.Count, "B").End(xlUp).Row
Cells(2, "D").Resize(LR - 1, 1).Formula = "=YourFormula" 'For D2
End Sub

lenze

This is perfect - thanks Lenze!