This is silly - 'virtual' blank cells!

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
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:

Code:
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!
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What you describe is not possible. This:

Selection.End(xlUp).Select

will select the last nonblank row. And your Do loop can only ever select the cell below.

Notwithstanding that, why not just use this one liner?

Sheets("Overview").Range("A65500").End(xlUp).Offset(1, 0).Select
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Further to Andrew's post, why are you selecting anyway? Just use:

Sheets("Overview").Range("A65500").End(xlUp).Offset(1, 0).Copy Sheets("Whatever").Range("A1")
 

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
Thanks for your code suggestions....

Andrew - i know, it shouldnt be possible, but it is! There's no blanks / spaces / tabs etc in the cells, and yet they are seen as populated by excel :(
 

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453

ADVERTISEMENT

Also, would it be possible as an alternative to only copy populated rows from my source table?

Thanks!
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This will copy all the rows on Sheet1 where column A contains a constant to Sheet 2 starting in A1:

Code:
Worksheets("Sheet1").Columns(1).SpecialCells(xlCellTypeConstants).EntireRow.Copy Worksheets("Sheet2").Range("A1")
 

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453

ADVERTISEMENT

Hiya,

Ok, i think i've found out what the issue is.... the issue is caused by some formulas in my source cells. They are empty formulas, but even when pasted by values, cause excel to trigger that there's some sort of value in the destination cells :(
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If there is something in the cell End(xlUp) will stop at it.

It might help if you posted an example of the formula, with its cell reference.
 

thorpyuk

Well-known Member
Joined
Mar 14, 2006
Messages
1,453
Hi Andrew again!

The formula in my source cells are:

=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)


That's my 7 cells in the range A5:A11.

The cell A5 is always populated with a number obviously.... but the other cells nearly always have an empty value (blank). The problem is when i copy calls A5:A11, and past them into another sheet as values only, excel believes cells A6:A11 have something in them, whereas in fact they are completely blank :s
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
A cell that contains "" (null) is not the same as a blank cell, as far as Excel navigation or the VBA IsEmpty function are concerned. There are other ways to test for a blank cell (including one that contains ""):

Code:
Sub Test()
    With Worksheets("Sheet2")
        MsgBox IsEmpty(.Range("A6"))
        MsgBox Len(.Range("A6").Value) = 0
        MsgBox .Range("A6").Value = ""
    End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top