Copy and paste non blank rows from a range

[mcinnes01]

Hi,

I need to copy only the rows in a range that have data in them. For a basic explination, with overtime calcs if someone is part time say 20/35 and they do 20 hours overtime.

The first 15 hours are paid at time and get one GL Code
The other 5 are time and a half and get a different code.

On my input sheet this is all calculated and only fills in the relevant line in the table (essentially there are about 12 overtime types)

The way the form worked originally was that it copied the data from the single input line to the output tab, this one now copies from the new table I've created to the output tab and works fine...


However it copies the whole range of the table "I6:P18" and includes blank lines on the output table. How can I get it so it only copies lines out of that range that aren't blank?

This is the current copy code I am using:

Private Sub CommandButton2_Click()
Dim NR As Long
Dim InS As Worksheet
Dim OuS As Worksheet

Set InS = Sheets("INPUT LIST")
Set OuS = Sheets("OUTPUT LIST")
NR = OuS.Range("A" & Rows.Count).End(xlUp).Row + 1

InS.Range("I6:P18").Copy
OuS.Range("A" & NR).PasteSpecial xlPasteValues

Range("Emp,Manual,Role,OHrs,SHrs,Casual,CRate,AddType,AddHrs,FDate,TDate").Value = ""
End Sub

[snb]

Private Sub CommandButton2_Click()
  with Sheets("INPUT LIST").Range("I6:P18")
    .autofilter 1,"<>"""
    .copy Sheets("OUTPUT LIST").cells(rows.count,1).end(xlup).(2)
    .autofilter
  End With
End Sub

[mcinnes01]

Hi,

This line is showing up in red, I am on XL2003 could that be why?

    .copy Sheets("OUTPUT LIST").cells(rows.count,1).end(xlup).(2)

Would this type of thing possibly work

Private Sub CommandButton2_Click()
Dim NR As Long
Dim InS As Worksheet
Dim OuS As Worksheet

Set InS = Sheets("INPUT LIST")
Set OuS = Sheets("OUTPUT LIST")
NR = OuS.Range("A" & Rows.Count).End(xlUp).Row + 1

InS.Range("I6:P18").Select
Selection.Copy
OuS.Range("A" & NR).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False

Range("Emp,Manual,Role,OHrs,SHrs,Casual,CRate,AddType,AddHrs,FDate,TDate").Value = ""
End Sub

It isn't currently think I might have missed something

[snb]

there's a dot too much

.copy Sheets("OUTPUT LIST").cells(rows.count,1).end(xlup).(2)

improved:

Private Sub CommandButton2_Click()
  with Sheets("INPUT LIST").Range("I6:P18")
    .autofilter 1,"<>"""
    .copy Sheets("OUTPUT LIST").cells(rows.count,1).end(xlup)(2)
    .autofilter
  End With
End Sub

[mcinnes01]

Hi,

The code has run this time, however it doesn't seems to be working, there is the full area copied over and it has copied formulas where as before it was just pasting values I think.

[mcinnes01]

I think I know, why and I should have been clearer in my explination...

The table that is copied in the range "I6:P18" has formulas in each cell so it is blank if certain conditions aren't met and so it can calulate the various codes and values.

So I think the code would need to refer to cells with value "" rather than just empty cells

Sorry I hope this clears my poor explination up.

[romperstomper]

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

[mcinnes01]

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?

[romperstomper]

Sorry - I overlooked something. Change this:

For rowIndex = 6 To dataRange.Rows.Count

to this:

For rowIndex = 1 To dataRange.Rows.Count

[mcinnes01]

Thats great it works a treat!

What is the line I type to stop it flicking back and forth until it has completed.

Something like

screenupdating = false
code.....

screenupdating = true

Thanks again I really appreciated it, I've been banging my head for hours today over that!

[romperstomper]

application.screenupdating = false

[mcinnes01]

awesome!

thanks