Macro range

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Macro range

  1. #1
    Board Regular
    Join Date
    Dec 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro range

    I receive a sheet of data daily, containing a list of numbers. The number are of several digits long and I convert them all in to six digit numbers in column B with the following formula. =text(a1,000000) I then click on the bottom right corner of cell A1 and the formula populates down. I then Special pates (values) the result in to cell A1

    I have now recorded a macro to do this process, but when this was set up there were only 500 rows of data, so when I receive a list containing 800 rows of data the macro only works up to row 500.
    Can the macro be altered to accommodate as many rows as there are rows of data?

    The macro reads:

    Range("E3").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-3],""000000"")"
    Range("E3").Select
    Selection.AutoFill Destination:=Range("E3:E500"), Type:=xlFillDefault
    Range("E3:E500").Select
    Selection.Copy
    ActiveWindow.ScrollRow = 1
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("E3").Select
    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    42,657
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro range

    Try capturing the last row, something like:

    ' Capture last row using column A
    Dim myLastRow as Long
    myLastRow = Range("A65536").End(xlUp).Row

    Range("E3").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-3],""000000"")"
    Range("E3").Select
    Selection.AutoFill Destination:=Range("E3:E" & myLastRow), Type:=xlFillDefault
    Range("E3:E500").Select
    Selection.Copy
    ActiveWindow.ScrollRow = 1
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("E3").Select
    End Sub
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    2,314
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro range

    Or this shorter version;

    Code:
    With Range("E3:E" & Range("B65536").End(xlUp).Row)
    .Formula = "=TEXT(RC[-3],""000000"")"
    .NumberFormat = "@"
    .Value = .Value
    .Copy .Offset(0, -3)
    .Clear
    End With

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com