CODEWITHSUNDEEP
excelvba
wiro
wiro

Reputation: 138

Is there a VBA string to select copied cells that used to have a blank formula?

I have an excel file where everything is controlled via a macro. At one point I am moving files from one sheet to another where this data is stored as a backlog.

I then try to select blank cells and remove the row if true. But cells appear blank but are not.

So I am moving it from Sheet A to Sheet B. The data in Sheet A is being moved and pasted as values in Sheet B. The data being moved has two columns: Column A holds Item ID and column B holds a date when the item cease to exist (Cease Date).

In Sheet A, Cease Date is populated through a simple formula (=+IF(O5<>"";O5;N5)) where if there is no new Cease Date input, fetch from backlog.

Now, if there neither is no new Cease Date input nor is there any backlog, the cell is blank.

When the macro copy and paste (as values) the data from Sheet A into Sheet B, column B is populated by blank cells (as intended) but there is something invisible, for lack of better word. Almost like there would be a formatting or like when you can encounter hidden characters not seen other than by ANSI.

IF I select any of the empty cells and press delete, then run "Go to special..." and blanks, the cell gets selected by the function.

I'm using this line to remove blanks:

Columns("B").SpecialCells(xlBlanks).EntireRow.Delete

This is the segment of my code that copy/paste and handle the Cease Date section:

Sheets("Dashboard").Select
    Range("B3:Q400").Select
    Selection.Copy
    Sheets("CeaseDate").Select
    Range("F1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("G:T").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("F1:G1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("F:G").Select
    Selection.Delete Shift:=xlToLeft

    Columns("B").SpecialCells(xlBlanks).EntireRow.Delete 'Remove rows that does not contain a Cease Date <--- This does not work since it wont treat the blank cells as blank

'Range converting to date format

    Columns("B:B").Select
    Selection.NumberFormat = "m/d/yyyy"

    Columns("A:B").Select
    ActiveSheet.Range("A:B").RemoveDuplicates Columns:=Array(1, 2), _
        Header:=xlYes
    Selection.End(xlUp).Select

Upvotes: 0

Views: 326

Answers (3)

jordi Beyen
jordi Beyen

Reputation: 41

What you could try is also removing formatting. I have encountered this several times and it worked for me with this code. with this line all formats in blank cells are removed.

 Columns("B").SpecialCells(xlBlanks).EntireRow.ClearFormats

what you could also try is adding the .clearformats function to every delete part. so if you have 

sheet1.column("B").clearcontent

you could add

     sheet1.column("B").clearcontent
     sheet1.column("B").clearformats

Upvotes: 1

Chronocidal
Chronocidal

Reputation: 7951

If you have a cell that contains a formula, but the Value of that Formula is equivalent to ="", then it will display as Blank, and the COUNTBLANK function in Excel (or WorksheetFunction.CountBlank function in VBA) will call it Blank, but SpecialCells(xlBlanks) will not - because the Range.Formula property is not blank.

Here is a Function to retrieve cells with a Blank Value in a range:

Private Function GetNullValues(ByVal Target As Range) As Range
    Dim TestingArea As Range, TestingCell As Range
    For Each TestingArea In Target.Areas 'Loop through Areas in Target
        For Each TestingCell In TestingArea.Cells 'Loop through Cells in Area
            If TestingCell.Value = "" Then 'If Cell looks Blank
                If GetNullValues Is Nothing Then 'If first blank found
                    Set GetNullValues = TestingCell 'Start list
                Else 'If not first blank
                    Set GetNullValues = Union(GetNullValues, TestingCell) 'Add to list
                End If
            End If
    Next TestingCell, TestingArea 'This is the same as doing 2 Next lines
End Function

Use it like this: Set BlankCells = GetNullValues(Sheet1.Columns(2))

Upvotes: 1

wiro
wiro

Reputation: 138

So I found a solution to this, I cannot explain why the problem occurs but apparently it has been something occurring in Excel since at least version 2003.

Problem: When I copy cells and use paste-special-value, the cells that appeared blank will fail the test of =ISBLANK() and returning a FALSE value. Yet, there is nothing to copy from the cell or anything to mark. If I select the apparently blank cell and press delete or Backspace, =ISBLANK() will now return a TRUE value.

Solution:

  1. I select the area with the apparently blank cells
  2. Open Find/Replace function and leave the Find What: blank (no spaces or anything) and then in the Replace With: type in a string or word that you KNOW does not appear anywhere else in the spreadsheet.
  3. Click Replace All
  4. All apparently blank cells will be replaced with the word
  5. Now take the word and Find/Replace it with nothing
  6. The replaced cells will now be truly blank and pass the =ISBLANK() test

I originally found this (quite obscure) solution here

Upvotes: 1

Related Questions