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:

Please Login or Register  to view this content.

[snb]

Please Login or Register  to view this content.

[mcinnes01]

Hi,

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

Please Login or Register  to view this content.

Would this type of thing possibly work

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

[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.

[snb]

I couldn't open your example worksheet.....

[mcinnes01]

I',m not well up on the paste special functions in VBA is there one that will make it only paste values that don't equal "" even though there maybe a formula behind the ""?

The current code, only pastes values:

Please Login or Register  to view this content.

[mcinnes01]

Hang on I will delete the employee data out and upload one. Although I have a feeling it will be about 2mb...

[mcinnes01]

http://www.mediafire.com/?97wlguc746xcfl8

file is 2.37mb

[snb]

I think, based on the code you have, you don't need more than 20 rows to post here.

[mcinnes01]

I'm not sure I understand what you mean, 20 rows?

[snb]

Citation:

Please Login or Register  to view this content.

[mcinnes01]

If I use select non blanks and then selection.copy would that work?

I'm not quite sure how to select the non blank rows.

[mcinnes01]

I tried this but I'm still quite weak at coding, so it doesn't work, but it may be along the right lines and you may be able to spot the error.

Please Login or Register  to view this content.

[romperstomper]

You haven't actually copied anything to paste, nor have you incremented the NR variable after the paste.

Please Login or Register  to view this content.

[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:

Please Login or Register  to view this content.

to this:

Please Login or Register  to view this content.

[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]

Please Login or Register  to view this content.

[mcinnes01]

awesome!

thanks