Free Microsoft Excel 2013 Quick Reference

Macro Autofill to last row - how?

Hi there

I know little about writing VBA code, and this is probably a no-brainer to you gurus out there, so please be gentle.

I have formulas in column C, D & E that I want to autofill down in a macro to the last row in column B. The number of rows in column B may vary every time I complete this sheet.

I have turned the "relative reference" button on while creating the macro, but I see in the VBA code that the cell number still appears ie. "A1:C66". How do I make it go to the bottom of column B despite how many rows there are?

This is what is appearing in the code:

Range("C2:E2").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:C66")
ActiveCell.Range("A1:C66").Select

Many many thanks
Lisa


Post your answer or comment

comments powered by Disqus
I Have a calculation in M4 and I want it to autofill to the end of the data.
But the data could end in row 35 or 50 etc. How would I find the last row of
data and autofill the formula down to it

Thanks

Hello, could some one please help modify the code as shown below so that in column A of worksheet: Data to Text I need an AutoFill from cell A1 to the last row. The value in cell A1 needs to be: 1

If there are more than 1 records then I need an AutoFill from cell A1 to the last row. However, the value in cell A1 needs to be 1, value in cell A2 needs to be 2, value in cell A3 needs to be 3 etc...

Any assistance is greatly appreciated.

Kind regards,

Chris

Sub Data_to_Text()
'
' Data to Text Macro
' Macro recorded 9/02/2010
'

'
    Dim myRange As Range
    Dim lr As Long

    
    Set myRange = Selection
    
    Sheets("Data").Select
    
    For Each cell In myRange
        Range(cell.Address).Value = cell.Value
    Next cell
    
    Selection.Copy
    
    Sheets("Data to Text").Select
    
    ActiveSheet.Paste
    
    Columns("A:A").Select
    
    Application.CutCopyMode = False
    
    Selection.Insert Shift:=xlToRight
    
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "2"
               
    lr = Cells(Rows.Count, "a").End(xlUp).Row
    
    Range("A1:A2").Select
    
    Range(Cells(lr, "a"), Cells(lr, "a")). _
    AutoFill Range(Cells(lr, "a"), Cells(lr + 2, "a"))
           
    
End Sub


Hi everyone,

Newbie here and I searched for an answer on this forum but maybe I don't have the correct syntax.

I need a macro to go to the last row of data and insert data "123" in the row after the last row (the column will be Column A).

The only problem is, there are blank cells between some rows. I need to go to the absolute last row of data.

Help!

Hi,

I have two worksheets, worksheet1 = "SalesOrder",
worksheet2 = "Allocation".

Both Worksheets have column headers from A- AC.

I need to copy those data from SalesOrder to add in the list of Allocation at the last row of worksheet 2.

I tried recorded macro with relative reference but it says
"The Information cannot be pasted because the copy area and the paste area are not the same size and shape."

Code for Record Macro

Sub Macro16()
'
' Macro16 Macro
'

'
    ActiveCell.Offset(4, 0).Rows("1:1").EntireRow.Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("ShipmentAllocationTij3").Select
    Selection.End(xlDown).Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
End Sub

Code Which I tried to Write
Sub Macro()

Dim SO As Worksheet
Dim SA3 As Worksheet
Dim SA4 As Worksheet

Set SO = Sheets("SalesOrder")
Set SA3 = Sheets("ShipmentAllocation3")
Set SA4 = Sheets("ShipmentAllocation4")


   For Each ce In SO.Range("A2:A" & SO.Cells(Rows.Count,
"A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
        'What is the range I should copy to Add to last row of ShipmentAllocation
      ce.EntireRow.Copy Destination:=Worksheets("ShipmentAllocation3).Range()
        SATij3LastRow = Worksheets("ShipmentAllocation3").Range("G65536").End(xlUp).Row
    Next ce
End Sub()


Hi, everyone. I too wanted to use Msgbox to input to last row. I have an Add new account button executing the macro below, with intentions putting input in A14 and below.
Columns:
Account Make pmt Bill type Pmt Interval

Type & Pmt Interval are date validation lists containing options from rows 4 to 13. So rows 4 to 13 are hidden with "starting" row at 14. If I use the macro as is, it sends input to A4 only, even if I populate a4..a13 with filler. If those rows are unhidden and populated, the input goes to A14.

How can I use this macro or modify it to populate cells after the hidden rows above it? The code follows:

Public
Sub AddAcct()



Dim X As String
Dim Message As String

Message = "Enter the Account Name you want to add:"

TitleBarTxt = "Add New Account"

DefaultTxt = " "

X = InputBox(Message, TitleBarTxt, DefaultTxt)
If X <> "" Then
With Worksheets("Accounts")
.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = X
End With
End If
End Sub
Thanks in advance

Hi all

I want get a macro to reach to last row with full
of data
and in this example the last row it's (number 60)

Thanks
there are attached below

Hi guys/gals,.
newbe here,..

just started trying to get a little deeper into Excel to help me with edited large files,..

I am trying to record a macro that edits a rows data, and simply copies it to a new cell further along on the same row,.

but i then want it to move to the next row down, and apply the same macro to that run, and continue until there is no data in the last row,.

How do I get the macro to continue to the end of the data,.

hope I've explained this correctly,.
thanks in advance for any help, I'll beat away at this in mean time

Hello everyone.

I have a following problem with excel macro:

Every day I have to copy data from the first row (A1:DB1) in to first empty row in the sheet (for example A25:DB25).

Then I have to clear the data for the previous day in the next-to last row (A24:DB24 in this example). But , I have to leave the the data in a cell A24 i.e. I have to clear the data only from B24:DB24.

I am only a begginer with VBA so I looked on the net I found this macro:

 Worksheets("Daily data").Range("A1:DB1").Copy
    Worksheets("Daily data").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=True, Transpose:=False
        Application.CutCopyMode = False
        
         
    Worksheets("Daily data").Cells(Rows.Count, "A").End(xlUp).Offset(-1,
0).EntireRow.ClearContents
However, this macro deletes everything from next-to-last row (inlcuding the data in column A).

How can I modify it so it would clear data only from the B24:DB24 range ?

Any help is welcome.

With Regards,

Eriol

Hello,

I am trying to automate a spreadsheet used to extract information from a text file using Excel 2003. My imported text files reside in column AA and are of varying length (5000 to 60000 rows) with gaps in the data. I would like to be able to copy my formulas from row 2 in columns C to Z and paste them in all rows from 2 to the last row. I was considering looping 1 row at a time but not sure how to deal with the blank spaces in the data in column AA. Plus I've noticed that loops can be rather slow so I was wondering if there's any way to highlight first to last row in a spreadsheet.

Ideally I'm looking for the following functionality in VBA. Copy C2:Z2 > highlight C2 to Cx (x = last row with data in column AA) > paste

If it's easier to highlight entire rows then I'm sure I can move the data around to allow for it.

Any help on this would be greatly appreciated

Hi,

I would like to be able to select rows from the highlighted one to the
last row. How do I do that?

Thanks

Keith

I'm currently trying to write a macro that will bring the data to the last row. Simular to when you 2x click on the bottom right hand of a cell and it brings it to the last row. My problem is the macro records the last row and when add additional rows to the spreadsheet it still brings it to the original row recorded in the macro. Is there any code that will allow the data to copy to the last row regardless of amount of rows?

Thank you,
Ryan

I have a spreadsheet with up to 3000 rows. I have created a macro that subtotals column J and L based on changes in col. A.

I now need to create a range beginning with column "A2" thru Column "D2" to last row of data in column a, but offset (-1,0) due to the Grand Total Row. I then have a macro to do a Go To Special to fill the blanks. I am having trouble because each month the number of rows will change.

Good day everyone,

Please can I get some help with the request below:

I have a spreadsheet with about 120 rows of data ( Currently ). Rows 1:5 are headings.

from Row 6 - Some of the columns are for the user to enter data and others are formula.

The user can enter data in all the unlocked cells ( certain columns - effectively adding records ).

I would like a macro that - after the user has entered new data - unlocks the sheet, copies all formatting ( including conditional formatting) from row 6 down to the last row, and then copies all formulae ( cells that contain formulae only) from row 6 down to the last row. sorts the data from row 6 to the last row ( sort on column C - Ascending) , after the sort - select rows 7 to last row and copy-paste special-values, and then locks the sheet again.

The Last row is based on there being a value in Column A ( from row 6 )

I hope this explanation makes sense

Thanks, I would appreciate any help on this.

JVN

( I can mail a sample spreadsheet if required )

I am trying to create a macro that will copy a formula down to the last row in Column D. For some reason the formula line is giving me a compile error.

Can anyone tell me what I am doing wrong.

Public Sub CopyFormula()
Dim Last_Row As Long
Last_Row = Range("A" & Rows.Count).End(xlUp).Row
'Determines last blank cell in Column A

Range("D2:D" & Last_Row).Formula = "=Text(C2,"mmmm")"

'Copies formula down to last row in Column D

End Sub


Dear all,

I want to search someting like 042 in Column E from row 1 to Last Row. If found in any row the the row will be highlighted as yellow color

Is there any formula or macro etc ?

Please help me

FAROOQ

I getting a runtime error '1004'
Method 'Range of object' _Worksheet' failed
Any help would be appreciated!
Private Sub Worksheet_Change(ByVal Target As Range)
'===================================Column B Caps=============================================

'Target is an arguments which refers to the range being changed
If Target.Column = 2 Then ' column B
    'this check is to prevent the procedure from repeatedly calling itself
    If Not (Target.Text = UCase(Target.Text)) Then
        Target = UCase(Target.Text)
    End If
End If
'===========================================Sort========================================

 'Sort column B to last row has data
Dim LastRow As Range
Set LastRow = Range("B2").End(xlUp)
With ActiveSheet
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=.Range("B2"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:="RED,GREEN,BLUE", _
        DataOption:=xlSortNormal
    With .Sort
         .SetRange Range("B2:B" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
End Sub


Hi,

Looking for code that will find and copy all data from A2 to last row please.

Last date is at A17=1/18 (file attached). The following macro is to fill the next 3 blank cells but not working.
Plse check.

	VB:
	
 
Dim LastDate As Long 
LastRow = Cells(Rows.Count, "A").End(xlUp).Row 
LastDate = Range("A2").End(xlDown).Offset(3, 0).Row 
 
Selection.AutoFill Destination:=Range("A" & LastRow ,"A" & LastDate), Type:=xlFillDefault 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a Sheet that uses Imported Data from a Tab delimited text file.
3 columns from the text file update 3 columns in the sheet. The sheet also contains another 10 columns of values and formulas.

I run a macro to update the first 3 columns, but I need to autofill the other columns to the last updated row of the first 3 columns. I'd like to include the autofill with my uodate macro.

I am writing a macro. It's more complicated than I originally thought. What I
need to do is autofill a formula in a column to the last row with data. The
problem is the rows are dynamic. They start in a different place each time
and end in a different place. The range size will vary every time the macro
is used. Once the formula has calculated the neccessary numbers I want to
copy and paste the information back to another column. My problem is telling
excel which function I want to perform when the calculations will be made on
a moving target. I have the macro working to the first cell where the formula
is entered. Now I need to copy the formula to the last row that contains
information the formula can use. Please help. I have posted this previously
but it was days ago and still no answer. Here's a sample of my macro. This is
only a small part.

Sheets("BOMSized").Select
Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[6]/12"

--
A Waller

I have a large file but it varies in size each month. When i recorded a
macro the file seems to copy down the column in a fixed method as shown below-

Selection.AutoFill Destination:=Range("H2:H15952")
Range("H2:H15952").Select

I was wondering what i can insert in these areas so that it will AutoFill
automatically to the bottom row of column F no matter how many cells are
present.

Please help me out. Thanks

--
Message posted via http://www.officekb.com

I am writing a macro. It's more complicated than I originally thought. What I
need to do is autofill a formula in a column to the last row with data. The
problem is the rows are dynamic. They start in a different place each time
and end in a different place. The range size will vary every time the macro
is used. Once the formula has calculated the neccessary numbers I want to
copy and paste the information back to another column. My problem is telling
excel which function I want to perform when the calculations will be made on
a moving target. I have the macro working to the first cell where the formula
is entered. Now I need to copy the formula to the last row that contains
information the formula can use. Please help. I have posted this previously
but it was days ago and still no answer. Here's a sample of my macro. This is
only a small part.

Sheets("BOMSized").Select
Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[6]/12"

--
A Waller

Currently trying to put together a macro to automate a reconciliation of some positions my company holds.

In this macro we create a formula and then copy it down to the bottom row of data.
Now, of course, the macro records this as going from C2 to C72 (forexample).
I need to put code into my macro telling it to copy C2 down to the last row of actual data.

Can anyone help me with this?

Hi

I have having trouble here with this code below, as I have fill down range to AM11:AM55, but when i delete or keep adding new rows, an error appears saying out of range. So how can i get the fill down to fill to the last row i have?
Any ideas how to get this to work without error appearing?
Any ideas would help

Thanks matt


	VB:
	
 
Range("F11:F500").Select 
Selection.ClearContents 
Range("H11:AM500").Select 
Range("AM500").Activate 
Selection.ClearContents 
Range("AM3:AM5").Select 
Selection.Copy 
Range("AM11:AM13").Select 
ActiveSheet.Paste 
Range("AM11:AM13").Select 
Application.CutCopyMode = False 
Selection.AutoFill Destination:=Range("AM11:AM55) type:=xlFillDefault 
Range("AM11:AM55").Select 
Range("AM11:AM13").Select 
Selection.Borders(xlDiagonalDown).LineStyle = xlNone 
Selection.Borders(xlDiagonalUp).LineStyle = xlNone 
With Selection.Borders(xlEdgeLeft) 
    .LineStyle = xlContinuous 
    .Weight = xlThin 
    .ColorIndex = xlAutomatic 
End With 
With Selection.Borders(xlEdgeTop) 
    .LineStyle = xlContinuous 
    .Weight = xlThin 
    .ColorIndex = xlAutomatic 
End With 
With Selection.Borders(xlEdgeBottom) 
    .LineStyle = xlContinuous 
    .Weight = xlThin 
    .ColorIndex = xlAutomatic 
End With 
With Selection.Borders(xlEdgeRight) 
    .LineStyle = xlContinuous 
    .Weight = xlThin 
    .ColorIndex = xlAutomatic 
End With 
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone 
Selection.AutoFill Destination:=Range("AM11:AM55"), Type:=xlFillDefault 
Range("AM11:AM55").Select 
Range("A11:A13").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines



No luck finding an answer? You could always try Google.