+ Reply to Thread
Results 1 to 4 of 4

Moving down one row in a macro

  1. #1
    Cathy S.
    Guest

    Moving down one row in a macro

    I am an inexperienced Excel user. I have a report in Excel using A1 through
    A36000. I recorded the following macro. The macro works, but when I run it,
    the new information uses the same row, covering up the previous information.
    How can I get it to move down one row each time. (I want a1 through a20 to
    go across a row at c1, then a21 through a40 to appear on the next row (c2),
    etc.) If there is a way to get a macro to run through all 36000 cells at one
    time, instead of running this macro over and over--that would be great too!

    Sub Transpose()
    '
    ' Transpose Macro
    ' Macro recorded 1/4/2005 by cstokley
    '
    ' Keyboard Shortcut: Ctrl+t
    '
    Range("A1:A20").Select
    Selection.Copy
    Range("C1").Select
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
    SkipBlanks:= _
    False, Transpose:=True
    Range("A1:A20").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("C2").Select
    End Sub

    --
    Thanks in advance for your help,
    Cathy S.

  2. #2
    Guest

    Moving down one row in a macro

    hi,
    try this
    Sub macMoveUp() 'start
    Dim rng1 As Range 'declare a variable type
    Dim cng1 As Range 'declare a variable type
    Dim cng2 As Range 'declare a variable type
    Set rng1 = Range("A1")'assign a range to variable
    Set cng1 = Range("C1")'assign a range to variable
    Do While Not IsEmpty(rng1) 'set loop and a way out
    Set cng2 = cng1.Offset(1, 0) 'set up for drop down
    Range(rng1, rng1.Offset(19, 0))'Select range
    Selection.Copy 'copy range
    cng1.Select 'select destination
    Selection.PasteSpecial Paste:=xlPasteAll, _
    Operation:=xlnone, SkipBlanks:=False, Transpose:=True
    ' paste transposed
    Range(rng1, rng1.Offset(19, 0)).Select 'reselect range
    Application.CutCopyMode = False 'clear clipboard
    Selection.Delete Shift:=xlUp 'delete range
    Set cng1 = cng2 'drop down one row
    Set rng1 = Range("A1") 're set
    Loop 'do again
    msgbox("Macro complete") 'message stating macro done
    End Sub 'stop

    >-----Original Message-----
    >I am an inexperienced Excel user. I have a report in

    Excel using A1 through
    >A36000. I recorded the following macro. The macro

    works, but when I run it,
    >the new information uses the same row, covering up the

    previous information.
    >How can I get it to move down one row each time. (I want

    a1 through a20 to
    >go across a row at c1, then a21 through a40 to appear on

    the next row (c2),
    >etc.) If there is a way to get a macro to run through

    all 36000 cells at one
    >time, instead of running this macro over and over--that

    would be great too!
    >
    >Sub Transpose()
    >'
    >' Transpose Macro
    >' Macro recorded 1/4/2005 by cstokley
    >'
    >' Keyboard Shortcut: Ctrl+t
    >'
    > Range("A1:A20").Select
    > Selection.Copy
    > Range("C1").Select
    > Range("C1").Select
    > Selection.PasteSpecial Paste:=xlPasteAll,

    Operation:=xlNone,
    >SkipBlanks:= _
    > False, Transpose:=True
    > Range("A1:A20").Select
    > Application.CutCopyMode = False
    > Selection.Delete Shift:=xlUp
    > Range("C2").Select
    >End Sub
    >
    >--
    >Thanks in advance for your help,
    >Cathy S.
    >.
    >


  3. #3
    Cathy S.
    Guest

    RE: Moving down one row in a macro

    Sorry--please tell me what to do with this. I'm a beginner! Thank you for
    your time.

    "anonymous@discussions.microsoft.com" wrote:

    > hi,
    > try this
    > Sub macMoveUp() 'start
    > Dim rng1 As Range 'declare a variable type
    > Dim cng1 As Range 'declare a variable type
    > Dim cng2 As Range 'declare a variable type
    > Set rng1 = Range("A1")'assign a range to variable
    > Set cng1 = Range("C1")'assign a range to variable
    > Do While Not IsEmpty(rng1) 'set loop and a way out
    > Set cng2 = cng1.Offset(1, 0) 'set up for drop down
    > Range(rng1, rng1.Offset(19, 0))'Select range
    > Selection.Copy 'copy range
    > cng1.Select 'select destination
    > Selection.PasteSpecial Paste:=xlPasteAll, _
    > Operation:=xlnone, SkipBlanks:=False, Transpose:=True
    > ' paste transposed
    > Range(rng1, rng1.Offset(19, 0)).Select 'reselect range
    > Application.CutCopyMode = False 'clear clipboard
    > Selection.Delete Shift:=xlUp 'delete range
    > Set cng1 = cng2 'drop down one row
    > Set rng1 = Range("A1") 're set
    > Loop 'do again
    > msgbox("Macro complete") 'message stating macro done
    > End Sub 'stop
    >
    > >-----Original Message-----
    > >I am an inexperienced Excel user. I have a report in

    > Excel using A1 through
    > >A36000. I recorded the following macro. The macro

    > works, but when I run it,
    > >the new information uses the same row, covering up the

    > previous information.
    > >How can I get it to move down one row each time. (I want

    > a1 through a20 to
    > >go across a row at c1, then a21 through a40 to appear on

    > the next row (c2),
    > >etc.) If there is a way to get a macro to run through

    > all 36000 cells at one
    > >time, instead of running this macro over and over--that

    > would be great too!
    > >
    > >Sub Transpose()
    > >'
    > >' Transpose Macro
    > >' Macro recorded 1/4/2005 by cstokley
    > >'
    > >' Keyboard Shortcut: Ctrl+t
    > >'
    > > Range("A1:A20").Select
    > > Selection.Copy
    > > Range("C1").Select
    > > Range("C1").Select
    > > Selection.PasteSpecial Paste:=xlPasteAll,

    > Operation:=xlNone,
    > >SkipBlanks:= _
    > > False, Transpose:=True
    > > Range("A1:A20").Select
    > > Application.CutCopyMode = False
    > > Selection.Delete Shift:=xlUp
    > > Range("C2").Select
    > >End Sub
    > >
    > >--
    > >Thanks in advance for your help,
    > >Cathy S.
    > >.
    > >

    >


  4. #4
    Guest

    RE: Moving down one row in a macro

    hi again,
    sorry about that.
    tools>macro>macros or alt+F8
    enter a macro name in the name box then click create.
    the vb editor will come up.
    copy all of the code below EXCEPT:
    Sub macMoveUp()
    End Sub
    paste the copied code in to the vb editor between the sub
    and end sub.
    you might experience some problem with the paste part as
    is does not seem to be wraping properly.
    it should be 2 lines with a underscore _ at the end of the
    first line.
    hope this helps.
    I am leaving work now so good luck. i will check is post
    tomorrow if you have any more problems.

    >-----Original Message-----
    >Sorry--please tell me what to do with this. I'm a

    beginner! Thank you for
    >your time.
    >
    >"anonymous@discussions.microsoft.com" wrote:
    >
    >> hi,
    >> try this
    >> Sub macMoveUp() 'start
    >> Dim rng1 As Range 'declare a variable type
    >> Dim cng1 As Range 'declare a variable type
    >> Dim cng2 As Range 'declare a variable type
    >> Set rng1 = Range("A1")'assign a range to variable
    >> Set cng1 = Range("C1")'assign a range to variable
    >> Do While Not IsEmpty(rng1) 'set loop and a way out
    >> Set cng2 = cng1.Offset(1, 0) 'set up for drop down
    >> Range(rng1, rng1.Offset(19, 0))'Select range
    >> Selection.Copy 'copy range
    >> cng1.Select 'select destination
    >> Selection.PasteSpecial Paste:=xlPasteAll,

    _
    >> Operation:=xlnone, SkipBlanks:=False,

    Transpose:=True
    >> ' paste transposed
    >> Range(rng1, rng1.Offset(19, 0)).Select 'reselect

    range
    >> Application.CutCopyMode = False 'clear clipboard
    >> Selection.Delete Shift:=xlUp 'delete range
    >> Set cng1 = cng2 'drop down one row
    >> Set rng1 = Range("A1") 're set
    >> Loop 'do again
    >> msgbox("Macro complete") 'message stating macro done
    >> End Sub 'stop
    >>
    >> >-----Original Message-----
    >> >I am an inexperienced Excel user. I have a report in

    >> Excel using A1 through
    >> >A36000. I recorded the following macro. The macro

    >> works, but when I run it,
    >> >the new information uses the same row, covering up the

    >> previous information.
    >> >How can I get it to move down one row each time. (I

    want
    >> a1 through a20 to
    >> >go across a row at c1, then a21 through a40 to appear

    on
    >> the next row (c2),
    >> >etc.) If there is a way to get a macro to run through

    >> all 36000 cells at one
    >> >time, instead of running this macro over and over--

    that
    >> would be great too!
    >> >
    >> >Sub Transpose()
    >> >'
    >> >' Transpose Macro
    >> >' Macro recorded 1/4/2005 by cstokley
    >> >'
    >> >' Keyboard Shortcut: Ctrl+t
    >> >'
    >> > Range("A1:A20").Select
    >> > Selection.Copy
    >> > Range("C1").Select
    >> > Range("C1").Select
    >> > Selection.PasteSpecial Paste:=xlPasteAll,

    >> Operation:=xlNone,
    >> >SkipBlanks:= _
    >> > False, Transpose:=True
    >> > Range("A1:A20").Select
    >> > Application.CutCopyMode = False
    >> > Selection.Delete Shift:=xlUp
    >> > Range("C2").Select
    >> >End Sub
    >> >
    >> >--
    >> >Thanks in advance for your help,
    >> >Cathy S.
    >> >.
    >> >

    >>

    >.
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1