Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel: Adjust Macro to Fill Down

Posted on 2014-09-07
9
230 Views
Last Modified: 2014-09-08
Hello,

Below is my current macro code.

I am seeking help to fill down the 'A' rows shown in lines 35-42. The rows are to fill down to correspond with the content in row B (no blanks until the dataset ends).

Currently this code only fills down for 30 rows, and I'm not sure why.


Sub mac_copyunique()
'
' mac_copyunique Macro
' This macro copies all the formatted data, pastes it to the UniqueID page and removes any row with a duplicate address. This will therebycreate a Unique ID
    
    Dim uniquerow As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Unique ID")
    uniquerow = ws.Range("B" & Rows.Count).End(xlUp).Row

' This is the first part - copying all the data and pasting it to the Unique ID page
    Sheets("Closed Address reformatting").Select
    Columns("F:O").Select
    Selection.Copy
    Sheets("Unique ID").Select
    Range("B1").Select
    ActiveSheet.Paste
    Rows("1:5").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Cells.Select
    ActiveSheet.Range("$A:$J").RemoveDuplicates Columns:=6, Header:= _
        xlYes
    ActiveWorkbook.Worksheets("Unique ID").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Unique ID").Sort.SortFields.Add Key:=Range( _
        "I:I"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Unique ID").Sort
        .SetRange Range("A:J")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
' This is the second part, creating adding new content to column A - and filling down with sequential numbers.
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "ID"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A2:A3").AutoFill Destination:=Range("A2:A3" & uniquerow)
    
    End With
    Sheets("Closed Address reformatting").Select
    Range("E6").Select
End Sub

Open in new window

0
Comment
Question by:dabug80
  • 4
  • 3
  • 2
9 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40309212
Hi,

pls try

Range("A2:A3").AutoFill Destination:=Range("A2:A" & uniquerow)


Regards
0
 
LVL 1

Author Comment

by:dabug80
ID: 40309228
Thanks. I got the error:

Run-time error 1004: AutoFill method of Range class failed

The line of code you gave was highlighted in the debug
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40309266
What is the value of uniquerow at line 42?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:dabug80
ID: 40309302
It's defined on row 9.
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40309309
try this instead

    Range("A2:A" & uniquerow).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=1, Trend:=False
0
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 500 total points
ID: 40309399
Range("A2").AutoFill Destination:=Range("A2:A" & uniquerow), Type:=xlFillSeries

Open in new window


Kris
0
 
LVL 1

Author Comment

by:dabug80
ID: 40311150
Hello,

Sadly none of the suggestions successfully fill down the sequential numbers for column A (Sheet: Unique ID).

Attached is a sample workbook to show my data. There is only one macro.

I'm trying to get the code line (42) to fire correctly so that column A of the sheet Unique ID is filled down with sequential numbers all the way to the bottom of the dataset.

Cheers.

Sub mac_copyunique()
'
' mac_copyunique Macro
' This macro copies all the formatted data, pastes it to the UniqueID page and removes any row with a duplicate address. This will therebycreate a Unique ID
    
    Dim uniquerow As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Unique ID")
    uniquerow = ws.Range("B" & Rows.Count).End(xlUp).Row

' This is the first part - copying all the data and pasting it to the Unique ID page
    Sheets("Closed Address reformatting").Select
    Columns("F:O").Select
    Selection.Copy
    Sheets("Unique ID").Select
    Range("B1").Select
    ActiveSheet.Paste
    Rows("1:5").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Cells.Select
    ActiveSheet.Range("$A:$J").RemoveDuplicates Columns:=6, Header:= _
        xlYes
    ActiveWorkbook.Worksheets("Unique ID").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Unique ID").Sort.SortFields.Add Key:=Range( _
        "I:I"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Unique ID").Sort
        .SetRange Range("A:J")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
' This is the second part, creating adding new content to column A - and filling down with sequential numbers.
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "ID"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A2").AutoFill Destination:=Range("A2:A" & uniquerow), Type:=xlFillSeries
    
    End With
    Sheets("Closed Address reformatting").Select
    Range("E6").Select
End Sub

Open in new window

sample2.xlsm
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 40311209
THe code is working fine in the workbook you attached.

Kris
0
 
LVL 1

Author Comment

by:dabug80
ID: 40311232
You're right Kris! The code works when there's existing content on the sheet. When I delete the content on the sheet (Unique ID) then the fill down doesn't work correctly. But that's ok - since there will always be data on the sheet to override.

I will mark as solved
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question