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]

Hmmm.... maybe i could try convert my code Andrew.... here's the original for copying the data over:

Sheets("Projects").Select
Range("A5:A11").Select
Selection.Copy
Sheets("Overview").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Application.CutCopyMode = False

Will something like this work:

Dim rng as range
Sheets("Projects").Select
if Len(.Range("A6").Value) = 0 then rng = range("A5")
if Len(.Range("A6").Value) = 0 then goto 20
if Len(.Range("A7").Value) = 0 then rng = range("A5:A6")
if Len(.Range("A7").Value) = 0 then goto 20
if Len(.Range("A8").Value) = 0 then rng = range("A5:A7")
if Len(.Range("A8").Value) = 0 then goto 20
if Len(.Range("A9").Value) = 0 then rng = range("A5:A8")
if Len(.Range("A9").Value) = 0 then goto 20
if Len(.Range("A10").Value) = 0 then rng = range("A5:A9")
if Len(.Range("A10").Value) = 0 then goto 20
if Len(.Range("A11").Value) = 0 then rng = range("A5:A10")
if Len(.Range("A11").Value) = 0 then goto 20
rng = range("A5:A11")
20
rng.Copy
Sheets("Overview").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
Application.CutCopyMode = False

The only issues is that if someone doesn't enter their project details in sequential order! (eg, they put a name into A6 and A9....

 

[Andrew Poulsom]

Try:

Sub Test()
    Sheets("Projects").Range("A5:A11").SpecialCells(xlCellTypeFormulas, 1).Copy
    Sheets("Overview").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=True, Transpose:=False
    Application.CutCopyMode = False
End Sub

 

[thorpyuk]

Thanks Andrew, that works well on column A.

I tried to use it on a multiple column, and got an error "no cells were found" (there's no formulas, only dates in my new array....

Sheets("Projects").Range("B5:AG11").SpecialCells(xlCellTypeFormulas, 1).Copy

 

[Andrew Poulsom]

My code applied to your range A5:A11, which contained formulas that returned a number. You can't expect it to work in other scenarios.

If you want to select constants:

SpecialCells(xlCellTypeConstants, 1) Numbers
SpecialCells(xlCellTypeConstants, 2) Text
SpecialCells(xlCellTypeConstants, 3) Numbers and Text

 

[thorpyuk]

Thanks Andrew, although now, because i'm searching for text or values, it ignores my blank columns, and therefore pastes the data into consecutive cells.... whereas i need it to copy everything including empty cells to keep the data in the correct columns :s

Is it possible to get to that?

Thanks!

 

[thorpyuk]

I thought i better clarify exactly what i'm upto!

Column A from my source sheet contains my project numbers (unique), which now copy over correctly into Column A of my destination sheet.

Then the next 2 columns in my destination sheet are constants, which i am populating sucessfully using an offset function.

Then the next set of columns in my destination sheet (D:AI) i'll populate with columns B:AG from my source....

Thanks!

 

[thorpyuk]

*better bump it up!*

 

[Andrew Poulsom]

Is this what you want?

Sub Test()
    Dim Rng As Range
    Set Rng = Sheets("Projects").Range("A5:A11").SpecialCells(xlCellTypeFormulas, 1)
    Rng.Copy
    Sheets("Overview").Range("A1").PasteSpecial xlValues
    Set Rng = Rng.Offset(0, 1).Resize(, 32)
    Rng.Copy
    Sheets("Overview").Range("D1").PasteSpecial xlValues
End Sub

 

[thorpyuk]

Hi Andrew,
Thanks for the reply.

I get an error "application defined, or object defined error"

on the lineL:

Set Rng = Rng.Offset(0, 1).Resize(, 32)

 

[Andrew Poulsom]

I tested my code before posting it. I used the formulas you posted:

=MAX(Overview!A:A)+1
=IF(B6="","",A5+1)
=IF(B7="","",MAX(A5:A6)+1)
=IF(B8="","",MAX(A5:A7)+1)
=IF(B9="","",MAX(A5:A8)+1)
=IF(B10="","",MAX(A5:A9)+1)
=IF(B11="","",MAX(A5:A10)+1)

I did assume that the formulas that returned numbers were in a contiguous range, eg A5:A7, not eg A5, A7 and A9. I think you would get an error if the range is discontiguous. Is that your scenario?