Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Paste into Blank Rows (Excel 2003)

    I have a spreadsheet of 5000 rows.and fifteen columns. Row A is a header row of labels. There are several blank rows amongst the 5000 rows. I would like to copy row A into the blank rows. I have come up with the following code that needs some help.
    Sub FillEmptyRows()
    Dim rng As Range
    Dim lngRow As Long
    Set rng = Range("A1", Range("A65536").End(xlUp))
    For lngRow = rng.Rows.Count To 2 Step -1
    If rng(lngRow) = "" Then
    Copy rng(lngRow) = (A1 - J1)
    rng(lngRow).Paste
    End If
    Next lngRow
    End Sub

    Any suggestions would be appreciated

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

    Re: Paste into Blank Rows (Excel 2003)

    You can't use Copy that way. Copy is a method, just like Paste. Try this version:

    Sub FillEmptyRows()
    Dim lngMaxRow As Long
    Dim lngRow As Long
    lngMaxRow = Range("A65536").End(xlUp).Row
    For lngRow = 2 To lngMaxRow
    If Cells(lngRow, 1) = "" Then
    Range("1:1").Copy Destination:=Cells(lngRow, 1)
    End If
    Next lngRow
    End Sub

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

    Re: Paste into Blank Rows (Excel 2003)

    What do you define as a blank row? Hans' code will copy the header row to any row where the cell in column A is empty. If you have any rows where column A can be empty but other columns contain data, then Hans' code needs a little tweak to something like this.

    <pre>Sub FillEmptyRows()
    Dim lngMaxRow As Long
    Dim lngRow As Long
    lngMaxRow = Range("A65536").End(xlUp).Row
    For lngRow = 2 To lngMaxRow
    If Application.WorksheetFunction.CountA(Range(lngRow & ":" & lngRow)) = 0 Then
    Range("1:1").Copy Destination:=Cells(lngRow, 1)
    End If
    Next lngRow
    End Sub
    </pre>

    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paste into Blank Rows (Excel 2003)

    Hans, the answer worked perfectly. as the blank rows were completely blank from A-end. Thanks for he help

Posting Permissions

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