+ Reply to Thread
Results 1 to 21 of 21

Copy and paste non blank rows from a range

Hybrid View

  1. #1
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,985

    Re: Copy and paste non blank rows from a range

    You haven't actually copied anything to paste, nor have you incremented the NR variable after the paste.
    Private Sub CommandButton2_Click()
    Dim NR As Long
    Dim InS As Worksheet
    Dim OuS As Worksheet
    Dim newRow As Integer
    Dim colIndex As Integer
    Dim rowIndex As Integer
    Dim dataRange As Range
    
    
    Set InS = Sheets("INPUT LIST")
    Set OuS = Sheets("OUTPUT LIST")
    Set dataRange = InS.Range("I6:P18")
    NR = OuS.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    For rowIndex = 6 To dataRange.Rows.Count
            If Application.CountIf(dataRange.Rows(rowIndex), "") < dataRange.Columns.Count then
    datarange.rows(rowindex).copy
        OuS.Range("A" & NR).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
    NR = NR + 1
    End If
    Next rowIndex
    Range("Emp,Manual,Role,OHrs,SHrs,Casual,CRate,AddType,AddHrs,FDate,TDate").Value = ""
    End Sub
    Everyone who confuses correlation and causation ends up dead.

  2. #2
    Forum Contributor mcinnes01's Avatar
    Join Date
    05-25-2010
    Location
    Manchester
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    449

    Re: Copy and paste non blank rows from a range

    Hi,

    thanks that seems to be a bit closer to the mark, I think its overwritting the line on the output list tab.

    So I tested it where the entry produced 4 rows in the range "I6:p18" to copy and paste to the "OUTPUT LIST" and then I did another that create 3 row to copy and paste.

    When I checked the output list there was only 1 line instead of 7?

+ 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