Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find next empty row (Excel 2000)

    I have a spreadsheet on which payments are calculated. Every time before I calculate a new payment I copy the previously calculated payment into a list of previous payments. How can I have a macro copy the payment information into the next empty row in the list.

    Any help will be appreciated.

    Kobus
    Regards
    Kobus

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Find next empty row (Excel 2000)

    <P ID="edit" class=small>(Edited by sdckapr on 27-Apr-04 14:42. Cleaned up the code, to eliminate all the variables)</P>We need more specifics on what you need. Could you provide some more details?

    Are you always moving a particular cell in a worksheet into the next free row in a given column of a particular worksheet?

    This code (for example) will copy the value from Sheet1!A1 to the next available row in Col A of Sheet2 (change sheet names, starting cell and column as appropriate in the code.

    <pre>Option Explicit
    Sub CopyCalc()
    Dim wksSource As Worksheet
    Dim rSource As Range
    Dim wksDest As Worksheet
    Dim iColDest As Integer

    'Change these as desired
    Set wksSource = Worksheets("Sheet1")
    Set rSource = wksSource.Range("A1")
    Set wksDest = Worksheets("Sheet2")
    iColDest = 1

    wksDest.Cells(65536, iColDest).End(xlUp).Offset(1, 0).Value = _
    rSource.Value

    Set rSource = Nothing
    Set wksSource = Nothing
    Set wksDest = Nothing
    End Sub</pre>


    Steve
    PS. Though you could combine all into one line and use (again change as desired).

    <pre>Sub CopyCalc()
    Worksheets("Sheet2").Cells(65536, 1).End(xlUp).Offset(1, 0).Value = _
    Worksheets("Sheet1").Range("A1").value
    End Sub</pre>


  3. #3
    Star Lounger
    Join Date
    Jan 2004
    Posts
    77
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find next empty row (Excel 2000)

    Steve

    Thank you very much, you always make things look so easy. I have used your code as an outline and adapt it to suit my workbook. I actually copy all the information in the row to the new location, so a gave the range a name and then Special Paste the values into the new location.

    Thank you again for your time.

    Kobus
    Regards
    Kobus

Posting Permissions

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