Macro to autofill formulas to the last row with data

Try

Sub GL_String_Import_Access()
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C1").FormulaR1C1 = "Full Name"
    With Range("C2:C" & LR)
        .FormulaR1C1 = "=CONCATENATE(RC[-1],"" "",RC[-2])"
        .Value = .Value
    End With
End Sub

You could set it within the code to what you want...

Sub GL_String_Import_Access()
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C1").FormulaR1C1 = "Full Name"
    With Range("C2:C" & LR)
        .FormulaR1C1 = "=CONCATENATE(RC[-1],"" "",RC[-2])"
        .Value = .Value
        .Font.Name = "Calibri"
        .Font.Size = 12
    End With
End Sub

How often are you going to be doing this? Is it once a day, once a week, once a month, or more?

I don't mess with Access all that much, but two options that I know of.

While in Excel, highlight the range you want to import into Access with the header row >> Copy

With Access open >> right click on the All Access Objects pane on the left side of the screen and paste. Follow the on-screen instructions and all done.

The 2nd, with Access open >> create >> Macro >> TransferSpreadsheet

Not entirely sure the action arguments, but if you read the tips or do a quick google search you can probably knock it out rather easily.

Again, since I'm not up on this method I would have to do a google search myself to ensure I get it right.

I use the first method quite a bit and works great for my purposes.

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

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

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

Hi jeff,

Just wanted to follow up on this thread. I've been using your code to autofill till the last row of data. however, there's a minor issue when i imported the sheet to access.

so i currently only have 9200 rows of data in excel right, however when i import the excel to access, access adds an extra of 8 empty rows to the new table. so, in the end i end up with 9208 rows (records).

do you have any idea why this might happen?

thanks!

My first thought is to check the rows from the Excel sheet to determine the real end.

Press Ctrl + End in the Excel sheet and see where the end falls.

Hi Jeff,

I think you're right.

When I pressed Ctrl + End, it points me to the correct end (where the last data is, say it's row 50) in the excel workbook that I used when creating this code. However when I tried it with the other ones, it didn't point me to where the last data is. It stopped at row 50 regardless whether there's any data there or not. Why is that? Is there any way I can correct this?

Because of this, whenever I imported the sheet to access, access keeps on generating 50 rows or records regardless whether there are only 20 rows of data, etc.

Here's the full code that I have now. I have expanded your code (as you can see) Thanks!

Sub Account_String()
'
' Account_String Macro
'
' Keyboard Shortcut: Ctrl+g
'
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Columns("C:C").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C1").FormulaR1C1 = "Full Name"
    With Range("C2:C" & LR)
    .FormulaR1C1 = "=CONCATENATE(RC[-1],"" "",RC[-2])"
    .Value = .Value
    End With
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Columns("K:K").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("K1").FormulaR1C1 = "Department"
    With Range("K2:K" & LR)
    .FormulaR1C1 = "=LEFT(RC[-1],4)"
    .Value = .Value
    End With
    Columns("W:W").EntireColumn.AutoFit
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Columns("X:X").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("X1").FormulaR1C1 = "Region"
    With Range("X2:X" & LR)
    .FormulaR1C1 = "=LEFT(RC[-1],3)"
    .Value = .Value
    End With
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Columns("Z:Z").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("Z1").FormulaR1C1 = "Unit Center"
    With Range("Z2:Z" & LR)
    .FormulaR1C1 = "=LEFT(RC[-1],4)"
    .Value = .Value
    End With
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("AB1").FormulaR1C1 = "Account String"
    With Range("AB2:AB" & LR)
    .FormulaR1C1 = "=CONCATENATE(RC[-8],""-"",""000"",""-"",RC[-4],""-"",RC[-17],""-"",RC[-2])"
    .Value = .Value
    End With
    Columns("AB:AB").EntireColumn.AutoFit
    Selection.Copy
    Range("AB1").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("AB:AB").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Account String"
    Columns("C:C").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A:B,D:D,F:AA").Select
    Range("F1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("C:C").Select
    Selection.Cut
    Columns("A:A").Select
    ActiveSheet.Paste
    Columns("C:C").Select
    Selection.Delete Shift:=xlToLeft
    Columns("A:C").Select
    Columns("A:C").EntireColumn.AutoFit
    Range("A1").Select
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("C1").FormulaR1C1 = "Period"
    With Range("C2:C" & LR)
    .FormulaR1C1 = "=TODAY()"
    .Value = .Value
    End With
    Range("C2:C9219").Select
    Selection.NumberFormat = "[$-409]mmm-yy;@"
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("D1").FormulaR1C1 = "Period Date"
    With Range("D2:D" & LR)
    .FormulaR1C1 = "=TEXT(TODAY()-DAY(TODAY())+1,""m/d/yyyy"")"
    .Value = .Value
    End With
    Range("A1").Select
End Sub

I've always had good luck running this to reset the last cell. Select the last cell in your workbook and then run

Sub makelastcell() from here

Hi Jeff,

I am not sure I understand what the coding really means... I am fairly new on coding.... Does this mean I have to insert a new module?

Thanks

Yes, you can insert this code in a new module...

Highlight macro to copy >> Ctrl + C >> Open your workbook
Alt + F11 >> opens the Visual Basic Editor (VBE)
Ctrl + R >>opens the Project Explorer (if not already open on left side of screen)
Insert menu >> Module or Alt + I, M >> activates the Insert menu and inserts a Standard Module
Paste code >> Ctrl + V (right side of screen)
Alt + Q >> exits VBE and returns to Excel
Back in Excel >> place your cursor on the last cell >> Alt + F8 >> Macro Dialog Box >> Highlight macro >> Run

Note: If range of data is A1:G500 >> place cursor on G500

Hi Jeff,

so does that mean I will have to build this macro everytime the range of data is different? The spreadsheets that I'm gonna be using the macro on have different data ranges though... they all have different data end... (e.g. spreadhsheet 1 might end on row 50, spreadsheet 2 might end on row 20, spreadsheet 3 might end on row 60 and so forth)..

I found this code in another thread. Do you think this code below will work or is it just the same as your code? Thanks!

Sub lixiang()
Dim r As Integer
' Range(), you can change the column number you need, such as C1.
r = ActiveSheet.Range("A1").End(xlDown).Row
Range("C1").Select
Selection.AutoFill Destination:=Range("C1:C" & r)
End Sub

The Macro will find the end by using the variable LR as in post #4 and yes the code you posted in #15 is essentially the same.

Also, very rarely is it ever necessary to use select in your code.

http://www.tushar-mehta.com/excel/vb...rder/index.htm

Sounds good...glad you found a workable solution and thanks for the feedback

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

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

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