This is silly - 'virtual' blank cells!

[thorpyuk]

Hiya,

I'm trying to copy over some data from 1 sheet to another..... I have filled in some data in the range A5:AG11 on my source sheet, and i intend to copy over the lot (although actually, only row 5 may be populated), and paste into another sheet.

I have 2 problems: firstly, the columns in my 2nd sheet dont match up exactly with the format of my source sheet, so i need to copy & paste the data in 2 parts... column A into column A of my destination sheet, and columns B:AG into columns D:AI of my destination sheet.

Secondly, as i'm pasting all 6 rows, regardless of if they are blank or not, excel seems to paste in virtual blank cells, with no data or spaces in any trailing row.... so, for eg:

Sheets("Overview").Range("A65500").Select
    Selection.End(xlUp).Select
    Do While Not IsEmpty(Selection)
        Selection.Offset(1, 0).Select
    Loop

Now selects a blank cell about 5 cells down from the first 'proper' blank cell.

Does anyone know how to vanish these virtual spaces it's putting in?

Thanks!

 

[thorpyuk]

Hi Andrew,

Yes, the range was discontiguous in my example.... although that would not be the usual senario! I just wanted to account for every possible outcome really :s

 

[thorpyuk]

*bump*

 

[thorpyuk]

Bump

 

[Andrew Poulsom]

Bump

What's your question exactly?

 

[thorpyuk]

Sorry Andrew, thanks for tracking back...

is it possible to get this code:

    Set Rng = Sheets("Projects").Range("A5:A11").SpecialCells(xlCellTypeFormulas, 1)
    Rng.Copy
    Sheets("Overview").Range("A65500").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial xlValues
    Set Rng = Rng.Offset(0, 1).Resize(, 32)
    Rng.Copy
    Sheets("Overview").Range("D65500").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial xlValues

to work for a discontiguous range?

Thanks!

 

[Andrew Poulsom]

No, because Resize doesn't like discontiguous ranges.

 

[thorpyuk]

Ok, not to worry, i cant see anyone putting in something, then leaving a row, then inputting something else!


Thanks for your help on this one!