Thread: Fill down to last cell containing data

Hi
I am sure this is probably a quick fix but I can't get my head around the solution.

I have the following code:

    Sheets("Export").Select
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "=Import!R[7]C" ' = Date
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=Import!R[7]C&"" - ""&Import!R[4]C[1]" ' = Concatenate type & description
    Range("C3").Select
    ActiveCell.FormulaR1C1 = "=Import!R[7]C[1]" ' Debit
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "=Import!R[4]C[1]" ' Credit
' Fill down to next row
    Range("A3:D3").Select
    Selection.AutoFill Destination:=Range("A3:C4"), Type:=xlFillDefault
    Range("A3:D4").Select

What I need to do is to continue to fill down until " " (or a null value) is found within A*:D*


Please can anyone help me ?

Maybe:

Sub test()
Dim lLR As Long
With Sheets("Export")
    lLR = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A3:A" & lLR).FormulaR1C1 = "=Import!R[4]C" ' = Date()
    .Range("B3:B" & lLR).FormulaR1C1 = "=Import!R[4]C&"" - ""&Import!R[4]C[1]" ' = Concatenate type & description
    .Range("C3:C" & lLR).FormulaR1C1 = "=Import!R[4]C[1]" ' Debit
    .Range("D3:D" & lLR).FormulaR1C1 = "=Import!R[4]C[1]" ' Credit
End With
End Sub

Regards

Thanks for that.


I am not sure why, but this only brings in the first 3 records.

Hi I am struggling with this question.

TMShucks gave me a solution (thank you) and after solving my own coding error (just row numbers) , I now have that bringing in the first 4 rows of data, but no more.

My (revised) vba is thus:

Sub export()
Dim lLR As Long
With Sheets("Export")
    lLR = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A3:A" & lLR).FormulaR1C1 = "=Import!R[6]C" ' = Date()
    .Range("B3:B" & lLR).FormulaR1C1 = "=Import!R[6]C&"" - ""&Import!R[6]C[1]" ' = Concatenate type & description
    .Range("C3:C" & lLR).FormulaR1C1 = "=Import!R[6]C[1]" ' Debit
    .Range("D3:D" & lLR).FormulaR1C1 = "=Import!R[6]C[1]" ' Credit
End With
End Sub

My problem is that the worksheet "Import" has approx 30 rows (varies) and I need to bring them all into the "Export" sheet.



Any help very much appreciated.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Please fix the Code Tags, as requested ... in both the posts with code extracts.

Thanks

Originally Posted by TMShucks

Please fix the Code Tags, as requested ... in both the posts with code extracts.

Thanks

Tags added as requested

Thanks for fixing the code tags.

Amended code:

Sub export()
Dim lLR As Long
Application.ScreenUpdating = False
With Sheets("Import")
    lLR = .Range("A" & .Rows.Count).End(xlUp).Row
End With
With Sheets("Export")
    .Cells.ClearContents
    .Range("A3:A" & lLR - 4).FormulaR1C1 = "=Import!R[4]C" ' = Date()
    .Range("B3:B" & lLR - 4).FormulaR1C1 = "=Import!R[4]C&"" - ""&Import!R[4]C[1]" ' = Concatenate type & description
    .Range("C3:C" & lLR - 4).FormulaR1C1 = "=Import!R[4]C[1]" ' Debit
    .Range("D3:D" & lLR - 4).FormulaR1C1 = "=Import!R[4]C[1]" ' Credit
End With

'   SELECT RANGE FOR EXPORT TO MS MONEY
    Sheets("Export").Select
    Range("A1:D" & Range("A" & Rows.Count).End(xlUp).Row).Select
Application.ScreenUpdating = True
End Sub

Output:

HTML Code: 

			
			
26-Sep-11	Visa - SHOP		£1.00	 
26-Sep-11	Visa - SHOP		£1.00	 
26-Sep-11	Standing order - COMPANY	£1.00	 
26-Sep-11	Standing order - COMPANY	£1.00	 
27-Sep-11	Visa - SHOP		£0.00	£1.00
29-Sep-11	Cheque - SHOP		£1.00	 

The output starts in row 3; I trust this is what you want.

Regards, TMS

You're welcome.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save