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!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
 
Upvote 0
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")
 
Upvote 0
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 :(
 
Upvote 0
Also, would it be possible as an alternative to only copy populated rows from my source table?

Thanks!
 
Upvote 0
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")
 
Upvote 0
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 :(
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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
Back
Top