Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Sep 2004
    Location
    Kansas, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy Down Excel Rows (VB/VBA)

    I receive a text file daily from a report system. I copy it to excel. See file attached. The number of rows can change daily, i.e. number of employees and the products they sell change every day. I need to figure out how to copy down each employee ID and name. I also need to copy down the date for all rows. Thanks!

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Copy Down Excel Rows (VB/VBA)

    I don't do much Excel VBA, so this probably is not the most efficient way to do this. But it seems to work. Strangely, I can't seem to undo the effects of the procedure. Maybe this is a difference between Excel VBA and Word VBA?!
    <pre>Sub FillByCopyFromAbove()
    ' Create object reference to current sheet
    Dim sht As Worksheet
    Set sht = ActiveWorkbook.ActiveSheet
    ' Store dimensions of used area
    Dim lngColMax As Long, lngRowMax As Long
    With sht.UsedRange
    lngColMax = .Columns.Count
    lngRowMax = .Rows.Count
    End With
    ' Fill from top left down, across
    Dim lngColCount As Long, lngRowCount As Long
    With sht
    For lngColCount = 1 To lngColMax
    For lngRowCount = 2 To lngRowMax
    If IsEmpty(.Cells(lngRowCount, lngColCount)) Then
    .Cells(lngRowCount - 1, lngColCount).Copy
    .Cells(lngRowCount, lngColCount).PasteSpecial
    End If
    Next
    Next lngColCount
    End With
    ' Clear the copy outline
    Application.CutCopyMode = False
    ' Clean up objects
    If Not (sht Is Nothing) Then Set sht = Nothing
    End Sub
    </pre>

    It might be better to ascertain the maximum row and column by using the VBA equivalent of End,Right and End,Down rather than UsedRange, but if no one has messed with the sheet before you run the above, it should be fine.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Down Excel Rows (VB/VBA)

    Yes, unlike Word, most VBA actions in Excel purge your undo history. Just as a save does.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Down Excel Rows (VB/VBA)

    The code you used for finding the last row and column will return unexpected results in two curcumstances. If there are any empty rows at the top of the worksheet, or empty columns on the left side of the sheet, then the row and/or column number will be less than the last row or column used. Second, if any rows or columns have been deleted since the last time the workbook was saved, then the row and/or column number will be larger than the number for the last used row or column. You should replace this code:

    <pre> With sht.UsedRange
    lngColMax = .Columns.Count
    lngRowMax = .Rows.Count
    End With
    </pre>


    with this code:

    <pre> lngColMax = sht.Range("IV1").End(xlToLeft).Column
    lngRowMax = sht.Range("D65536").End(xlUp).Row
    </pre>

    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Sep 2004
    Location
    Kansas, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Down Excel Rows (VB/VBA)

    Thank you all for your help on this! I tried it at work today and it worked great! However, I discovered that my report can be pulled by date range and will list each day of data in one text file. Unfortunately, it sticks the date in the middle of the Products column! In the attached file I inserted two columns to include an office name and the date, but I can't figure out the code to find the "Date:" cells in column E and move them three cells to the left in column B. Your help is really appreciated! This code saves me a great deal of time!! Thanks!

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Down Excel Rows (VB/VBA)

    Try something like this:

    <pre>Public Sub FixDate()
    Dim lLastRow As Long, I As Long
    With Worksheets("Sheet1")
    lLastRow = .Range("E65536").End(xlUp).Row - 1
    For I = 0 To lLastRow
    If UCase(Left(.Range("E1").Offset(I, 0).Value, 5)) = "DATE:" Then
    .Range("E1").Offset(I, -3).Value = Right(.Range("E1").Offset(I, 0).Value, 8)
    .Range("E1").Offset(I, 0).Value = ""
    End If
    Next I
    End With
    End Sub
    </pre>

    Legare Coleman

  7. #7
    New Lounger
    Join Date
    Sep 2004
    Location
    Kansas, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Down Excel Rows (VB/VBA)

    Legare, You exceeded my expectations on this one!! I thought I would have to insert a column with the right function to retrieve just the date, but you included it in the code. This works perfect! Thanks!

  8. #8
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Down Excel Rows (VB/VBA)

    I found this post and for the most part it's similar to my situation. The main difference is that my data is populated by a process that works from bottom to top instead of top to bottom as the example attachement shows. I've changed the script to work in reverse (from bottom to top) but I can't get it to copy more than one line when it finds a empty cell. I've attached a file that is an example of my situation. Also, how can I expand the script to look at column D also? Any assistance will be greatly appreciated.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Down Excel Rows (VB/VBA)

    The inner loop (on intRowCount) should work bottom from top too. And the IsEmpty test doesn't always do what you want. Try this variation:
    <code>
    Sub FillByCopyFromBelow()
    Dim lngColMax As Long, lngRowMax As Long
    Dim lngColCount As Long, lngRowCount As Long
    ' Create object reference to current sheet
    'Store dimensions of used area
    lngColMax = Range("IV6").End(xlToLeft).Column
    lngRowMax = Range("D65536").End(xlUp).Row
    ' Fill from top left down, across
    With sht
    For lngColCount = 1 To lngColMax
    ' Determines what row script starts on
    For lngRowCount = lngRowMax To 6 Step -1
    If Trim(Cells(lngRowCount, lngColCount)) = "" Then
    Cells(lngRowCount, lngColCount) = _
    Cells(lngRowCount + 1, lngColCount)
    End If
    Next lngRowCount
    Next lngColCount
    End With
    End Sub
    </code>
    The inner loop works upwards: For lngRowCount = lngRowMax To 6 Step -1
    And the test checks whether the value of a cell (after trimming away spaces, to be on the safe side) is an empty string: If Trim(.Cells(lngRowCount, lngColCount)) = "" Then

  10. #10
    Star Lounger
    Join Date
    Jul 2001
    Location
    U.S
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Down Excel Rows (VB/VBA)

    Hans,

    Thank you so much. With a little tweaking this code is working perfectly for me. It's amazing how close you can be yet the answer still escapes you. The step argument is a powerful feature and I'm sure I will get much use out of it for this and other projects.

Posting Permissions

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