Excel is empty but xlup does select them

[paultje_bos]

Hi,

I have a sheet with formulas that leaves the cell blank when the result is null (iferror). But when I copy the cells (including blank cells with a formula) and I paste them as value, the empty cells will still be selected when I use ctrl+shift+down. But if I click on the empty cell there is nothing in there, not even the formula (because I pasted it as value).

For some reason Excel still "see" something in the cell.

Reason I ask is because I have a macro that selects data from one sheet (including blank cells with formula), copy and paste them on a different sheet on the first empty row. But the next time I run the marco, the first empty row is not the real empty row because of the problem I mentioned above.

Hope I make some clear. If somebody would have an anwser or work around for me, that would be great.

 

[RoryA]

This is unfortunately what happens. You need an additional bit of code to loop through the target range after you pasted there and clear the contents of any cells whose value is zero length.

 

[bobsan42]

Well the thing is ...
the cell is not really blank after you copied a value in it, even when the value is a zero-lenght string like "".
So the solution would be to do a check of the values you pasted and do .ClearContents on each cell which has a value equal to "".
This will wipe all "balnk" cells and really make them blank.

 

[paultje_bos]

Well the thing is ...
the cell is not really blank after you copied a value in it, even when the value is a zero-lenght string like "".
So the solution would be to do a check of the values you pasted and do .ClearContents on each cell which has a value equal to "".
This will wipe all "balnk" cells and really make them blank.

Could you help me a bit more, not an expert, so could you give me the vba code?

 

[RoryA]

Post your current macro code.

 

[paultje_bos]

Sub Macro4()
'
' Macro4 Macro
'
'
    Sheets("Sheet2").Select
    Columns("A:K").SpecialCells(xlConstants).EntireRow.Select
    Selection.Copy
    Sheets("FOD MATERIALS IN STORE").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Here I should have the code to clearcontents if cell is blank

    Sheets("GR Import").Select
    Range("A2:G1129").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("A2").Select
    Sheets("FOD MATERIALS IN STORE").Select
End Sub

 

[Snakehips]

paultje_bos,

Try......

Sub Macro4()
'
' Macro4 Macro
'
'
Sheets("Sheet2").Select
Columns("A:K").SpecialCells(xlConstants).EntireRow.Select
Selection.Copy
Sheets("FOD MATERIALS IN STORE").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Selection.TextToColumns


Sheets("GR Import").Select
Range("A2:G1129").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("A2").Select
Sheets("FOD MATERIALS IN STORE").Select
End Sub

Hope that helps.